In a MySQL query, convert a timestamp to a date.

Problem

In MySQL, I’d like to convert a timestamp to a date.

I’d like to save the user.registration field as a yyyy-mm-dd format in the text file.

My SQL is as follows:

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

I also tried converting dates with:

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

Before writing the text file, I echo the result, and I get:

What is the best way to convert that field to a date?

Asked by remyremy

Solution #1

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

Answered by Yatin

Solution #2

You can cast it solely to obtain a date.

cast(user.registration as date)

and date format to get a specified format

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

Answered by juergen d

Solution #3

Create a table with an integer timestamp like follows:

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

Insert some values

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

Take a look

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

Convert the integer to a timestamp using the following formula:

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)

Convert it to a readable format as follows:

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)

Answered by Eric Leschinski

Solution #4

If the registration field is of the type TIMESTAMP, you should be able to do as follows:

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

and the date should be displayed as yyyy-mm-dd

Answered by cs0lar

Solution #5

Simply use the DATE function in MySQL:

mysql> select DATE(mytimestamp) from foo;

Answered by Morey

Post is based on https://stackoverflow.com/questions/9251561/convert-timestamp-to-date-in-mysql-query