Convert unix timestamp to human readable date in MySQL select

If you are using INT for storing timestamps in your mysql database, very often you are dealing with weird numbers that don’t tell you much, e.g.

mysql> select time from example LIMIT 0,5;
+------------+
| time       |
+------------+
| 1480496034 |
| 1477817634 |
| 1472547234 |
| 1469868834 |
| 1467276834 |
+------------+
5 rows in set (0.00 sec)

To convert it into human readable format, use

mysql> select FROM_UNIXTIME(time) from example LIMIT 0,5;
+---------------------+
| FROM_UNIXTIME(time) |
+---------------------+
| 2016-11-30 09:53:54 |
| 2016-10-30 09:53:54 |
| 2016-08-30 10:53:54 |
| 2016-07-30 10:53:54 |
| 2016-06-30 10:53:54 |
+---------------------+
5 rows in set (0.00 sec)

You can even change the date format freely using DATE_FORMAT function ( www.w3schools.com/sql/func_date_format.asp )

mysql> select FROM_UNIXTIME(time), DATE_FORMAT(FROM_UNIXTIME(time), '%j. day of year on %a') from example LIMIT 0,5;
+---------------------+-----------------------------------------------------------+
| FROM_UNIXTIME(time) | DATE_FORMAT(FROM_UNIXTIME(time), '%j. day of year on %a') |
+---------------------+-----------------------------------------------------------+
| 2016-11-30 09:53:54 | 335. day of year on Wed                                   |
| 2016-10-30 09:53:54 | 304. day of year on Sun                                   |
| 2016-08-30 10:53:54 | 243. day of year on Tue                                   |
| 2016-07-30 10:53:54 | 212. day of year on Sat                                   |
| 2016-06-30 10:53:54 | 182. day of year on Thu                                   |
+---------------------+-----------------------------------------------------------+
5 rows in set (0.00 sec)

Write a Comment

Comment

  1. I think this is one of the most important information for me.
    And i am glad reading your article. But wanna remark on some general things, The site
    style is ideal, the articles is really nice : D. Good job, cheers

Webmentions

  • how long does bactrim take to work for uti 2021-03-24

    how long does bactrim take to work for uti

    how long does bactrim take to work for uti

  • ciprofloxacin 0.3 ear drops dosage 2021-03-24

    ciprofloxacin 0.3 ear drops dosage

    ciprofloxacin 0.3 ear drops dosage

  • l'azithromycine en collyre 2021-03-24

    l’azithromycine en collyre

    l’azithromycine en collyre

  • long-term effects of cymbalta on the brain 2021-03-24

    long-term effects of cymbalta on the brain

    long-term effects of cymbalta on the brain

  • ibuprofen and zoloft 2021-03-24

    ibuprofen and zoloft

    ibuprofen and zoloft

  • fluoxetine long half life 2021-03-24

    fluoxetine long half life

    fluoxetine long half life

  • semaglutide before and after 2021-03-24

    semaglutide before and after

    semaglutide before and after

  • gout lisinopril 2021-03-24

    gout lisinopril

    gout lisinopril

  • furosemide drank 2021-03-24

    furosemide drank

    furosemide drank

  • nolvadex serm 2021-03-24

    nolvadex serm

    nolvadex serm

  • how to get off lyrica 2021-03-24

    how to get off lyrica

    how to get off lyrica

  • apo-gabapentin wiki 2021-03-24

    apo-gabapentin wiki

    apo-gabapentin wiki

  • tadalafil 40 mg with dapoxetine 60 mg 2021-03-24

    tadalafil 40 mg with dapoxetine 60 mg

    tadalafil 40 mg with dapoxetine 60 mg

  • cialis professional review 2021-03-24

    cialis professional review

    cialis professional review

  • codeine pharmacy nz 2021-03-24

    codeine pharmacy nz

    codeine pharmacy nz

  • ventolin inhaler inhouse pharmacy 2021-03-24

    ventolin inhaler inhouse pharmacy

    ventolin inhaler inhouse pharmacy

  • cialis tadalafil cheapest online 2021-03-24

    cialis tadalafil cheapest online

    cialis tadalafil cheapest online

  • cialis suppliers uk cheap 2021-03-24

    cialis suppliers uk cheap

    cialis suppliers uk cheap

  • is there a generic cialis available in the us 2021-03-24

    is there a generic cialis available in the us

    is there a generic cialis available in the us

  • does generic cialis work 2021-03-24

    does generic cialis work

    does generic cialis work

  • hq pharmacy online 365 2021-03-24

    hq pharmacy online 365

    hq pharmacy online 365

  • best online cialis pharmacy reviews 2021-03-24

    best online cialis pharmacy reviews

    Convert unix timestamp to human readable date in MySQL select

  • 24 hour pharmacy near me 2021-03-24

    24 hour pharmacy near me

    Convert unix timestamp to human readable date in MySQL select

  • internet pharmacy vicodin 2021-03-24

    internet pharmacy vicodin

    Convert unix timestamp to human readable date in MySQL select

  • essay writing service law school 2021-03-24

    essay writing service law school

    Convert unix timestamp to human readable date in MySQL select

  • write my essay 4 me 2021-03-24

    write my essay 4 me

    Convert unix timestamp to human readable date in MySQL select

  • best essays writing service 2021-03-24

    best essays writing service

    Convert unix timestamp to human readable date in MySQL select

  • write my essay south park 2021-03-24

    write my essay south park

    Convert unix timestamp to human readable date in MySQL select

  • can someone write my essay 2021-03-24

    can someone write my essay

    Convert unix timestamp to human readable date in MySQL select

  • can you write my essay 2021-03-24

    can you write my essay

    Convert unix timestamp to human readable date in MySQL select

  • top essay writing service 2021-03-24

    top essay writing service

    Convert unix timestamp to human readable date in MySQL select

  • descriptive essay help 2021-03-24

    descriptive essay help

    Convert unix timestamp to human readable date in MySQL select

  • 100mg sildenafil price 2021-03-24

    100mg sildenafil price

    Convert unix timestamp to human readable date in MySQL select