Reputation: 11308

Convert Unix timestamp into human readable date using MySQL

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.

Upvotes: 290

Answers (10)

Dishant Kumar Yadav

Reputation: 13

whenever we are using FROM_UNIX like here in

SELECT
  FROM_UNIXTIME(timestamp) 
FROM 
  your_table;

make sure to know whether in what format timestamp is, like if its in seconds, we can use the above

But if its in milliseconds, we have to divide it by 1000

SELECT 
 FROM_UNIXTIME(column_name / 1000) AS readableDate 
FROM 
 table_name;

Upvotes: 1

SuspectT3

Reputation: 11

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

Upvotes: 1

User

Reputation: 65951

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

Upvotes: 148

CristiC

Reputation: 22698

Use FROM_UNIXTIME():

SELECT
  FROM_UNIXTIME(timestamp) 
FROM 
  your_table;

Upvotes: 523

Gunnar Bernstein

Reputation: 6222

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.

Upvotes: 1

josh.trow

Reputation: 4901

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.

Upvotes: 7

Akash gupta

Reputation: 336

Easy and simple way:

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

Upvotes: 2

elbowlobstercowstand

Reputation: 3951

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.

Upvotes: 25