MySQL 쿼리에서 타임 스탬프를 날짜로 변환

MySQL 쿼리에서 타임 스탬프를 날짜로 변환

---

timestampMySQL에서 날짜를 날짜 로 변환하고 싶습니다 .

user.registration 필드를 텍스트 파일로 형식화하고 싶습니다 yyyy-mm-dd.

내 SQL은 다음과 같습니다.

$sql = requestSQL("SELECT user.email, 
                   info.name, 
                   FROM_UNIXTIME(user.registration),
                   info.news
                   FROM user, info 
                   WHERE user.id = info.id ", "export members");

또한 날짜 변환을 시도했습니다.

DATE_FORMAT(user.registration, '%d/%m/%Y')
DATE(user.registration)

텍스트 파일을 작성하기 전에 결과를 에코하고 다음을 얻습니다.

email1; 이름 1; DATE_FORMAT (사용자 등록, '% d / % m / % Y'); news1

email2; 이름 2; 뉴스 2

해당 필드를 날짜로 어떻게 변환합니까?

---

DATE_FORMAT(FROM_UNIXTIME(`user.registration`), '%e %b %Y') AS 'date_formatted'

---

MYSQL에서 타임 스탬프를 날짜로 변환

정수 타임 스탬프를 사용하여 테이블을 만듭니다.

mysql> create table foo(id INT, mytimestamp INT(11));
Query OK, 0 rows affected (0.02 sec)

일부 값을 삽입

mysql> insert into foo values(1, 1381262848);
Query OK, 1 row affected (0.01 sec)

구경하다

mysql> select * from foo;
+------+-------------+
| id   | mytimestamp |
+------+-------------+
|    1 |  1381262848 |
+------+-------------+
1 row in set (0.00 sec)

숫자를 타임 스탬프로 변환하십시오.

mysql> select id, from_unixtime(mytimestamp) from foo;
+------+----------------------------+
| id   | from_unixtime(mytimestamp) |
+------+----------------------------+
|    1 | 2013-10-08 16:07:28        |
+------+----------------------------+
1 row in set (0.00 sec)

읽을 수있는 형식으로 변환하십시오.

mysql> select id, from_unixtime(mytimestamp, '%Y %D %M %H:%i:%s') from foo;
+------+-------------------------------------------------+
| id   | from_unixtime(mytimestamp, '%Y %D %M %H:%i:%s') |
+------+-------------------------------------------------+
|    1 | 2013 8th October 04:07:28                       |
+------+-------------------------------------------------+
1 row in set (0.00 sec)

---

그냥 날짜를 얻으려면 할 수 cast있습니다

cast(user.registration as date)

특정 형식 사용을 얻으려면 date_format

date_format(registration, '%Y-%m-%d')

SQLFiddle 데모

---

은 IF registration필드 유형 참으로 TIMESTAMP당신은 할 수 있어야한다 :

$sql = "SELECT user.email, 
           info.name, 
           DATE(user.registration), 
           info.news
      FROM user, 
           info 
     WHERE user.id = info.id ";

등록은 yyyy-mm-dd로 표시되어야합니다.

---

Just use mysql's DATE function:

mysql> select DATE(mytimestamp) from foo;

---

You should convert timestamp to date.

select FROM_UNIXTIME(user.registration, '%Y-%m-%d %H:%i:%s') AS 'date_formatted'

FROM_UNIXTIME

---

If you are getting the query in your output you need to show us the code that actually echos the result. Can you post the code that calls requeteSQL?

For example, if you have used single quotes in php, it will print the variable name, not the value

echo 'foo is $foo'; // foo is $foo

This sounds exactly like your problem and I am positive this is the cause.

Also, try removing the @ symbol to see if that helps by giving you more infromation.

so that

$SQL_result = @mysql_query($SQL_requete); // run the query

becomes

  $SQL_result = mysql_query($SQL_requete); // run the query

This will stop any error suppression if the query is throwing an error.

---

I did it with the 'date' function as described in here :

(SELECT count(*) as the-counts,(date(timestamp)) as the-timestamps FROM `user_data` WHERE 1 group BY the-timestamps)

---

Try:

SELECT strftime("%Y-%d-%m", col_name, 'unixepoch') AS col_name

It will format timestamp in milliseconds to yyyy-mm-dd string.

---

FROM_UNIXTIME(unix_timestamp, [format]) is all you need

FROM_UNIXTIME(user.registration, '%Y-%m-%d') AS 'date_formatted'

FROM_UNIXTIME gets a number value and transforms it to a DATE object,
or if given a format string, it returns it as a string.

The older solution was to get the initial date object and format it with a second function DATE_FORMAT... but this is no longer necessary

참고URL : https://stackoverflow.com/questions/9251561/convert-timestamp-to-date-in-mysql-query