Convert Unix timestamp into human readable date using MySQL

mysql unix-timestamp
King Julien picture King Julien · Jun 7, 2011 · Viewed 323.9k times · Source

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.

Answer

CristiC picture CristiC · Jun 7, 2011

Use FROM_UNIXTIME():

SELECT
  FROM_UNIXTIME(timestamp) 
FROM 
  your_table;

See also: MySQL documentation on FROM_UNIXTIME().

Related questions

MySQL convert date string to Unix timestamp

How do I convert the following format to unix timestamp? Apr 15 2012 12:00AM The format I get from DB seems to have AM at the end. I've tried using the following but it did not work: CONVERT(DATETIME, Sales.SalesDate, 103) AS …

Read More
MYSQL query between two timestamps

I have the following entry in my DB table eventName(varchar 100) -> myEvent date(timestamp) -> 2013-03-26 09:00:00 and I am trying to use the following query; SELECT * FROM eventList WHERE `date` BETWEEN UNIX_TIMESTAMP(1364256001) AND UNIX_TIMESTAMP(1364342399) …

Read More
How should unix timestamps be stored in int columns?

I have a logging table that will contain millions of writes for statistical reasons. All the columns are int foreign keys. I am also going to add a timestamp column for each row. Given that DATETIME takes 8bits - I …

Read More