Advertisement
Advertisement


Convert Unix timestamp into human readable date using MySQL


Question

Is there a MySQL function which can be used to convert a Unix timestamp into a human readable date? I have one field where I save Unix times and now I want to add another field for human readable dates.

2012/06/27
1
228
6/27/2012 8:06:17 AM

Accepted Answer

Use FROM_UNIXTIME():

SELECT
  FROM_UNIXTIME(timestamp) 
FROM 
  your_table;

See also: MySQL documentation on FROM_UNIXTIME().

2019/01/04
406
1/4/2019 4:42:03 AM

What's missing from the other answers (as of this writing) and not directly obvious is that from_unixtime can take a second parameter to specify the format like so:

SELECT
  from_unixtime(timestamp, '%Y %D %M %H:%i:%s')
FROM 
  your_table
2019/05/10

I think what you're looking for is FROM_UNIXTIME()

2011/06/07

Need a unix timestamp in a specific timezone?

Here's a one liner if you have quick access to the mysql cli:

mysql> select convert_tz(from_unixtime(1467095851), 'UTC', 'MST') as 'local time';

+---------------------+
| local time          |
+---------------------+
| 2016-06-27 23:37:31 |
+---------------------+

Replace 'MST' with your desired timezone. I live in Arizona thus the conversion from UTC to MST.


Why bother saving the field as readable? Just us AS

SELECT theTimeStamp, FROM_UNIXTIME(theTimeStamp) AS readableDate
               FROM theTable
               WHERE theTable.theField = theValue;

EDIT: Sorry, we store everything in milliseconds not seconds. Fixed it.

2018/10/09

You can use the DATE_FORMAT function. Here's a page with examples, and the patterns you can use to select different date components.

2011/06/07

Source: https://stackoverflow.com/questions/6267564
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]