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.

[Date and time functions](http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html) in the mySQL manual — Pekka, Jun 07 '11 at 15:25
Possible duplicate of [How to convert timestamp to datetime in MySQL?](http://stackoverflow.com/questions/5362874/how-to-convert-timestamp-to-datetime-in-mysql) — Álvaro González, Jan 29 '16 at 10:24

score 508 · Accepted Answer · edited Jan 04 '19 at 04:42

508

Use FROM_UNIXTIME():

SELECT
  FROM_UNIXTIME(timestamp) 
FROM 
  your_table;

See also: MySQL documentation on FROM_UNIXTIME().

edited Jan 04 '19 at 04:42

Brad

159,648
54
349
530

answered Jun 07 '11 at 15:28

CristiC

22,068
12
57
89

3

Here's the official docs for `from_unixtime`: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_from-unixtime – DACrosby Mar 11 '15 at 21:36
1

Sadly there's no `TO_UNIXTIME`, but instead `UNIX_TIMESTAMP`. – Olle Härstedt May 22 '20 at 12:45
@OlleHärstedt it is sad indeed. – Peter Chaula Jun 25 '21 at 05:27
Won't that be `null` if the UNIX timestamp is above years 2038? At least, if you insert FROM_UNIXTIME({unix timestamp above 2039}) it will return null. It is a known issue. – Mar 02 '22 at 04:10

score 142 · Answer 2 · edited May 10 '19 at 01:57

142

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

edited May 10 '19 at 01:57

Community

1
1

answered Nov 11 '13 at 00:11

User

62,498
72
186
247

28

Very minor issue, but %h is hours in 12-hour format, which then requires %p for completeness (AM/PM). Or %H gives hours in 24-hour format. – tlum May 30 '15 at 16:14

score 33 · Answer 3 · answered Jun 07 '11 at 15:26

33

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

answered Jun 07 '11 at 15:26

Ted Hopp

232,168
48
399
521

score 25 · Answer 4 · answered Jun 28 '16 at 07:55

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.

score 7 · Answer 5 · edited Oct 09 '18 at 06:20

7

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.

edited Oct 09 '18 at 06:20

Alexis Wilke

19,179
10
84
156

answered Jun 07 '11 at 15:27

josh.trow

4,861
20
31

score 5 · Answer 6 · answered Jun 07 '11 at 15:27

5

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

answered Jun 07 '11 at 15:27

Briguy37

8,342
3
33
53

score 2 · Answer 7 · answered Oct 03 '18 at 10:03

2

Easy and simple way:

select from_unixtime(column_name, '%Y-%m-%d') from table_name

answered Oct 03 '18 at 10:03

Akash gupta

316
4
4

score 1 · Answer 8 · answered Dec 21 '18 at 12:08

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.

score 1 · Answer 9 · edited May 10 '22 at 16:14

1

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

edited May 10 '22 at 16:14

JoSSte

2,953
6
34
54

answered Nov 04 '21 at 19:53

SuspectT3

11
1

Convert Unix timestamp into human readable date using MySQL

9 Answers9

Need a unix timestamp in a specific timezone?

Linked

Related