How to convert timestamp to datetime in MySQL?

Question

How to convert 1300464000 to 2011-03-18 16:00:00 in MySQL?

Please search for existing questions/answers before you post a new question - this topic has already been covered in some detail. — John Parker
– John Parker, Commented 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
– Felix Kling, Commented Mar 19, 2011 at 15:05
possible duplicate of Convert timestamp to a readable date during query — John Parker
– John Parker, Commented Mar 19, 2011 at 15:06

Community · Accepted Answer · 2016-10-18 14:26:48Z

243

Use the FROM_UNIXTIME() function in MySQL

Remember that if you are using a framework that stores it in milliseconds (for example Java's timestamp) you have to divide by 1000 to obtain the right Unix time in seconds.

edited Oct 18, 2016 at 14:26

CommunityBot

11 silver badge

answered Mar 19, 2011 at 15:02

Richard Tuin

4,5722 gold badges23 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Janus Troelsen Over a year ago

SELECT from_unixtime( TIMESTAMP( "2011-12-01", "22:01:23.048" ) ) doesn't work. why? using v5.6

cnvzmxcvmcx Over a year ago

@JanusTroelsen SELECT from_unixtime( unix_timestamp(TIMESTAMP( "2011-12-01", "22:01:23.048" ) ) ) works. TIMESTAMP() does not return an integer value.

Chris Thompson Over a year ago

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.

Luc Over a year ago

Also tested to work in MariaDB. If you want only the date, for example, then add a second parameter like select from_unixtime(1234567890, '%W %D %M %x');

herb · Accepted Answer · 2020-02-20 15:57:35Z

84

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

This is the ultimate solution if the given date is in encoded format like 1300464000

edited Feb 20, 2020 at 15:57

herb

1013 bronze badges

answered Apr 5, 2013 at 7:53

Kingshuk Deb

1,7393 gold badges27 silver badges42 bronze badges

2 Comments

JNP Web Developer Over a year ago

Thanks for this. To include the time: DATE_FORMAT(FROM_UNIXTIME(`orderdate`), '%d-%m-%Y %H:%i:%s') as "Date" FROM `orders`

Kyle Over a year ago

I'm pretty sure DATE_FORMAT(FROM_UNIXTIME(orderdate), '%Y-%m-%d') as "Date" FROM orders would be the ultimate solution :D

ksvendsen · Accepted Answer · 2014-02-21 17:32:40Z

17

To answer Janus Troelsen comment

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

answered Feb 21, 2014 at 17:32

ksvendsen

4945 silver badges6 bronze badges

3 Comments

Mantisse Over a year ago

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.

Paweł Stankowski Over a year ago

Please do not answer this way to the different question than the original one.

Nico Haase Over a year ago

This does not answer the original question in any way

3pitt · Accepted Answer · 2023-06-12 23:35:53Z

14

You can use

select from_unixtime(1300464000,"%Y-%m-%d %h %i %s") from table;

Overall, there are two pertinent methods

from_unixtime()
unix_timestamp()

edited Jun 12, 2023 at 23:35

3pitt

9612 gold badges16 silver badges25 bronze badges

answered May 24, 2018 at 7:44

Dilraj Singh

1,01912 silver badges12 bronze badges

2 Comments

tanner burton Over a year ago

I would use this format for military time "%Y-%m-%d %H %i %s"

Kevin Over a year ago

as @tannerburton says it's H not h as the OP wanted 24 hour time format

Leo · Accepted Answer · 2017-08-14 11:06:46Z

4

SELECT from_unixtime( UNIX_TIMESTAMP(fild_with_timestamp) ) from "your_table"
This work for me

answered Aug 14, 2017 at 11:06

Leo

4095 silver badges7 bronze badges

Collectives™ on Stack Overflow

How to convert timestamp to datetime in MySQL?

5 Answers 5

4 Comments

2 Comments

3 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

2 Comments

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related