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

User · Answer

Easy and simple way   select from unixtime column name    Y- m- d   from table name

User · Answer

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

User · Answer

I think what you re looking for is FROM UNIXTIME

User · Answer

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

User · Answer

Need a unix timestamp in a specific timezone   Here s a one liner if you have quick access to the mysql cli   mysql gt  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

User · Answer

Use FROM UNIXTIME     SELECT   FROM UNIXTIME timestamp   FROM    your table    See also   MySQL documentation on FROM UNIXTIME

User · Answer

You can use the DATE FORMAT function   Here s a page with examples  and the patterns you can use to select different date components

User · Answer

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

[mysql] Convert Unix timestamp into human readable date using MySQL

Examples related to mysql

Examples related to unix-timestamp