163

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

compile-fan
  • 16,885
  • 22
  • 59
  • 73
  • 2
    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 '11 at 15:04
  • 2
    Have you had a look at the documentation already? It's all there: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html – Felix Kling Mar 19 '11 at 15:05
  • 2
    possible duplicate of [Convert timestamp to a readable date during query](http://stackoverflow.com/questions/3978939/convert-timestamp-to-a-readable-date-during-query) – John Parker Mar 19 '11 at 15:06

5 Answers5

234

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.

Community
  • 1
  • 1
Richard Tuin
  • 4,484
  • 2
  • 19
  • 18
  • 1
    `SELECT from_unixtime( TIMESTAMP( "2011-12-01", "22:01:23.048" ) )` doesn't work. why? using v5.6 – Janus Troelsen Feb 04 '14 at 18:44
  • 4
    @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 '14 at 10:40
  • 4
    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 '15 at 16:43
81
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

herb
  • 103
  • 3
Kingshuk Deb
  • 1,700
  • 2
  • 26
  • 40
  • 4
    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 '17 at 12:30
  • 1
    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 '18 at 12:56
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

ksvendsen
  • 494
  • 5
  • 6
  • 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 '19 at 18:00
  • 1
    Please do not answer this way to the different question than the original one. – Paweł Stankowski May 21 '20 at 22:13
  • This does not answer the original question in any way – Nico Haase May 22 '20 at 07:07
12

You can use

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

Overall, there are two pertinent methods

  1. from_unixtime()
  2. unix_timestamp()
3pitt
  • 899
  • 13
  • 21
Dilraj Singh
  • 951
  • 10
  • 12
4

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

Leo
  • 359
  • 4
  • 6