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