Convert Unix timestamp into human readable date using MySQL
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
Accepted Answer
Use FROM_UNIXTIME()
:
SELECT
FROM_UNIXTIME(timestamp)
FROM
your_table;
See also: MySQL documentation on FROM_UNIXTIME()
.
2019/01/04
Read more... Read less...
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
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.
2016/06/28
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
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]