Convert Unix timestamp into human readable date using MySQL
MysqlUnix TimestampMysql Problem Overview
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.
Mysql Solutions
Solution 1 - Mysql
Use FROM_UNIXTIME()
:
SELECT
FROM_UNIXTIME(timestamp)
FROM
your_table;
See also: MySQL documentation on FROM_UNIXTIME()
.
Solution 2 - Mysql
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
Solution 3 - Mysql
I think what you're looking for is FROM_UNIXTIME()
Solution 4 - Mysql
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.
Solution 5 - Mysql
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.
Solution 6 - Mysql
You can use the DATE_FORMAT function. Here's a page with examples, and the patterns you can use to select different date components.
Solution 7 - Mysql
Easy and simple way:
select from_unixtime(column_name, '%Y-%m-%d') from table_name
Solution 8 - Mysql
Since I found this question not being aware, that mysql always stores time in timestamp fields in UTC but will display (e.g. phpmyadmin) in local time zone I would like to add my findings.
I have an automatically updated last_modified field, defined as:
`last_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
Looking at it with phpmyadmin, it looks like it is in local time, internally it is UTC
SET time_zone = '+04:00'; // or '+00:00' to display dates in UTC or 'UTC' if time zones are installed.
SELECT last_modified, UNIX_TIMESTAMP(last_modified), from_unixtime(UNIX_TIMESTAMP(last_modified), '%Y-%c-%d %H:%i:%s'), CONVERT_TZ(last_modified,@@session.time_zone,'+00:00') as UTC FROM `table_name`
In any constellation, UNIX_TIMESTAMP and 'as UTC' are always displayed in UTC time.
Run this twice, first without setting the time_zone.
Solution 9 - Mysql
If you would like to convert time AND display the data in a specific format you can use this string.
date_format(convert_tz(from_unixtime(TIMESTAMP), 'UTC', 'DESIRED TZ'), '%m/%d/%y')
where you add convert_tz
to a date_format string. the %m/%d/%y
being month/day/year
.
you can find all the specific formats here https://www.w3schools.com/sql/func_mysql_date_format.asp