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

  • child porn 2021-03-24

    Convert unix timestamp to human readable date in MySQL select

    https://www.enfpainting.com/the-importance-of-building-a-strong-brand-identity/

  • anan─▒n am─▒ 2021-03-24

    Convert unix timestamp to human readable date in MySQL select

    https://www.estudiocbaselga.com/el-cuadro-paseo-a-orillas-del-mar-de-joaquin-sorolla-como-inspiracion-para-el-diseno-de-una-vivienda/

  • animal porn 2021-03-24

    Convert unix timestamp to human readable date in MySQL select

    http://www.lauragiordano.com/opening-night-at-la-scala/

  • animal porn 2021-03-24

    Convert unix timestamp to human readable date in MySQL select

    http://www.lauragiordano.com/opening-night-at-la-scala/

  • buy sildenafil 20 mg 2021-03-24

    buy sildenafil 20 mg

    buy sildenafil 20 mg

  • cost of ivermectin 3mg tablets 2021-03-24

    cost of ivermectin 3mg tablets

    cost of ivermectin 3mg tablets

  • how much does ivermectin cost 2021-03-24

    how much does ivermectin cost

    how much does ivermectin cost

  • ivermectin 3mg dosage 2021-03-24

    ivermectin 3mg dosage

    ivermectin 3mg dosage

  • levitra dosage 60 mg 2021-03-24

    levitra dosage 60 mg

    levitra dosage 60 mg

  • cialis online pills 2021-03-24

    cialis online pills

    cialis online pills

  • ivermectin lotion 2021-03-24

    ivermectin lotion

    ivermectin lotion

  • spironolactone cause weight gain 2021-03-24

    spironolactone cause weight gain

    spironolactone cause weight gain

  • abilify medication 2021-03-24

    abilify medication

    abilify medication

  • tamsulosin abz 0 4 mg 2021-03-24

    tamsulosin abz 0 4 mg

    tamsulosin abz 0 4 mg

  • how long does protonix take to work 2021-03-24

    how long does protonix take to work

    how long does protonix take to work

  • acarbose fda 2021-03-24

    acarbose fda

    acarbose fda

  • robaxin 750 mg 2021-03-24

    robaxin 750 mg

    robaxin 750 mg

  • remeron for appetite stimulation 2021-03-24

    remeron for appetite stimulation

    remeron for appetite stimulation

  • actos thiazolidinediones 2021-03-24

    actos thiazolidinediones

    actos thiazolidinediones

  • celecoxib contraindication 2021-03-24

    celecoxib contraindication

    celecoxib contraindication

  • how to wean off celexa 10mg 2021-03-24

    how to wean off celexa 10mg

    how to wean off celexa 10mg

  • amitriptyline overdose treatment 2021-03-24

    amitriptyline overdose treatment

    amitriptyline overdose treatment

  • can i give my dog baby aspirin 2021-03-24

    can i give my dog baby aspirin

    can i give my dog baby aspirin

  • depakote for depression 2021-03-24

    depakote for depression

    depakote for depression

  • diltiazem nursing implications 2021-03-24

    diltiazem nursing implications

    diltiazem nursing implications

  • mounjaro and contrave together 2021-03-24

    mounjaro and contrave together

    mounjaro and contrave together

  • patient education on flomax 2021-03-24

    patient education on flomax

    patient education on flomax

  • blood pressure medicine cozaar 2021-03-24

    blood pressure medicine cozaar

    blood pressure medicine cozaar

  • water deprivation test ddavp 2021-03-24

    water deprivation test ddavp

    water deprivation test ddavp

  • simvastatin and ezetimibe in aortic stenosis seas trial 2021-03-24

    simvastatin and ezetimibe in aortic stenosis seas trial

    simvastatin and ezetimibe in aortic stenosis seas trial

  • 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