sql - Convert timestamp to date in MySQL query

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
I want to convert a timestamp in MySQL to a date.
I would like to format the user.registration field into the text file as a yyyy-mm-dd .
Here is my SQL:
$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 the date conversion with:
DATE_FORMAT(user.registration, '%d/%m/%Y')
DATE(user.registration)
I echo the result before to write the text file and I get :
email1;name1;DATE_FORMAT(user.registration, '%d/%m/%Y');news1
email2;name2;news2
How can I convert that field to date?
                Its saving the file exactly like that?  What is the code you're using to run the sql query?
– seanbreeden
                Feb 12, 2012 at 18:49
                As of MySQL 5.6.23, I find that the value to FROM_UNIXTIME() should be entered directly: i.e.,   DATE_FORMAT(FROM_UNIXTIME(user.registration), '%e %b %Y') AS 'date_formatted'
– JESii
                Apr 15, 2016 at 13:50
                SELECT * FROM users WHERE DATE_FORMAT(FROM_UNIXTIME(users.user_created_at),'%Y-%b-%e') = '2015-03-06' is this correct format or should i modify this?
– Dheeraj Thedijje
                Mar 7, 2018 at 11:49
                @DheerajThedijje this shows up as 2018-Nov-6. You may be looking for '%Y-%m-%d' as you'd format it in PHP (date('Y-m-d',$row->user_created_at)) - this (both variants, SQL and PHP) shows up as 2018-11-06
– Chris S.
                Nov 7, 2018 at 9:19
                In case you want to add the time in this fashion "hh:mm:ss", add this to the format string ' %H:%i:%s'
– luis.ap.uyen
                Feb 18, 2020 at 8:54
                So is this storing the datetime of that timestamp from the perspective of London, or New York, or where the trigger is run or where the client is run?
– William Entriken
                Nov 2, 2022 at 16:22
                It gives the same result, cast(user.registration as date) is displayed instead of the value of the field
– remyremy
                Feb 12, 2012 at 19:10
Make the table with an integer timestamp:
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 number to a timestamp:
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 into a readable format:
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)
If the registration field is indeed of type TIMESTAMP you should be able to just do:
$sql = "SELECT user.email, 
           info.name, 
           DATE(user.registration), 
           info.news
      FROM user, 
     WHERE user.id = info.id ";
and the registration should be showing as yyyy-mm-dd
                I tried but I get the same result: DATE(user.registration) is display instead of the value of the field
– remyremy
                Feb 12, 2012 at 19:09
                as someone suggested, are you able to run the query successfully using mysql client directly? I haven't heard of the requeteSQL function, what exactly does it do?
– cs0lar
                Feb 12, 2012 at 19:17
                Unfortunately I am not able to use a mysql client directly, I don't have my own server... I have provided requeteSQL, basically it just run the query and test if error to send emails...
– remyremy
                Feb 12, 2012 at 19:22
                Actually I realized my field was a BIGINT type and not a TIMESTAMP. Only the content was a timestamp (1328649722), so that's why it didn't work. Now everything is good with FROM_UNIXTIME!
– remyremy
                Feb 13, 2012 at 17:55
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
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.
If you want to change the datatype of the column, you can simply convert first from TIMESTAMP to INT:
ALTER TABLE table_name MODIFY column_name INT;
And then INT to DATE:
ALTER TABLE table_name MODIFY column_name DATE;
But, if you didn't mean to change a column, but wanted SELECT only, then you can use date() function:
SELECT date(your_timestamp_column) FROM your_table;
I want to convert a record 1580707260
Usually, I am using online timestamp converter
Want to showcase it in the query result
Please try this
DATE_FORMAT(FROM_UNIXTIME(field name from table), '%e %b %Y') 
AS 'display name for result'
You can use this command FROM_UNIXTIME(unix_timestamp, [format]); but sometimes timestamp is in a long value that you have to remove 3 last values to 0.
for instance you use this command from_unixtime(e.EVENT_TIME/1000);
this way solve my problem.
        Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.