How to convert timestamp to datetime in MySQL?

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 Please search for existing questions/answers before you post a new question - this topic has already been covered in some detail. – John Parker Mar 19, 2011 at 15:04 Have you had a look at the documentation already? It's all there: dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html – Felix Kling Mar 19, 2011 at 15:05


    SELECT from_unixtime( TIMESTAMP(  "2011-12-01",  "22:01:23.048" ) )

doesn't work. why? using v5.6 – Janus Troelsen Feb 4, 2014 at 18:44 @JanusTroelsen


    SELECT from_unixtime( unix_timestamp(TIMESTAMP(  "2011-12-01",  "22:01:23.048" ) ) )

works.


    TIMESTAMP()

does not return an integer value. – cnvzmxcvmcx Jul 11, 2014 at 10:40 Just because I've done this and been confused at the output: MySQL stores Unix time in seconds, whereas a lot of other frameworks store it in milliseconds (i.e. Java's timestamp). So just remember to divide by 1000 if you're using this function on data coming from somewhere that uses millis for Unix time. – Chris Thompson Oct 26, 2015 at 16:43 Thanks for this. To include the time:


    DATE_FORMAT(FROM_UNIXTIME(`orderdate`), '%d-%m-%Y %H:%i:%s') as "Date" FROM `orders`

– JNP Web Developer Apr 23, 2017 at 12:30 I'm pretty sure DATE_FORMAT(FROM_UNIXTIME(


    orderdate

), '%Y-%m-%d') as "Date" FROM


    orders

would be the ultimate solution :D – Kyle Dec 18, 2018 at 12:56

Use UNIX_TIMESTAMP instead of TIMESTAMP

SELECT from_unixtime( UNIX_TIMESTAMP(  "2011-12-01 22:01:23.048" ) )
The TIMESTAMP function returns a Date or a DateTime and not a timestamp, while UNIX_TIMESTAMP returns a unix timestamp
                seems clear to compare a unix time from a unix time. I use SELECT (CAST(DATE_FORMAT(from_unixtime(UNIX_TIMESTAMP(CURRENT_TIMESTAMP)), '%Y-%m-%d') as DATE) < CAST('2019-05-02' AS DATE)) ; to compare dates, I replace 2019-05*02 a formatted datetime object in php. Thnaks.
– Mantisse
                Apr 12, 2019 at 18:00
select from_unixtime(1300464000,"%Y-%m-%d %h %i %s") from table;
Overall, there are two pertinent methods
from_unixtime()
unix_timestamp()
        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.