Converting Unix Timestamp into a Custom Human Readable time using mySQL

Question

I have the following table in a MySQL database:

+----------+---------------------+-----------------+---------------+
| id       | firstName           | lastName        | tstamp        |
+----------+---------------------+-----------------+---------------+
| 133      | James               | Mosher          | 1589470887919 |  
| 145      | Bill                | Turner          | 1589470888235 | 
| 146      | Jeremy              | Vine            | 1589470888592 |  
| 152      | Ramon               | Jesus           | 1589470889319 |
+----------+---------------------+-----------------+---------------+

I want to be able to see the tstamp column in a human readable date time format. I tried the following query but it didn't work and gave me a blank column. Anyone has any idea or see what I am doing wrong?

SELECT id, firstName, lastName, tstamp, FROM_UNIXTIME(tstamp, '%Y %D %M %H:%i:%s') AS timeAndDate FROM students;

Result:

+----------+---------------------+-----------------+---------------+---------------+
| id       | firstName           | lastName        | tstamp        | timeAndDate   |
+----------+---------------------+-----------------+---------------+---------------+
| 133      | James               | Mosher          | 1589470887919 |               | 
| 145      | Bill                | Turner          | 1589470888235 |               |
| 146      | Jeremy              | Vine            | 1589470888592 |               |
| 152      | Ramon               | Jesus           | 1589470889319 |               |
+----------+---------------------+-----------------+---------------+---------------+

Example -> https://www.db-fiddle.com/f/nfSnTKfNYdBcbs8pDgbcdQ/0

FWIW, I would handle this in application code, formatting the time to suit the locale of the reader — Strawberry
– Strawberry, Commented Jul 6, 2020 at 12:03
@Strawberry - Yes, you are right but I do not own the application code that generates this. Only have MySql access to data for now. — Saffik
– Saffik, Commented Jul 6, 2020 at 12:04

forpas · Accepted Answer · 2020-07-06 12:07:48Z

1

The timestamps contain milliseconds.
You can remove them if you divide by 1000:

SELECT id, firstName, lastName, tstamp, 
  FROM_UNIXTIME(tstamp / 1000, '%Y %D %M %H:%i:%s') AS timeAndDate 
FROM myTable;

See the demo.

answered Jul 6, 2020 at 12:07

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Strawberry · Accepted Answer · 2020-07-06 12:09:54Z

1

See comments, but I guess you're after something like this...

SELECT DATE_FORMAT(FROM_UNIXTIME(1589470887919/1000),'%Y %d %m %H:%i:%s') x;
+---------------------+
| x                   |
+---------------------+
| 2020 14 05 16:41:27 |
+---------------------+

answered Jul 6, 2020 at 12:09

Strawberry

34k14 gold badges43 silver badges57 bronze badges

2 Comments

forpas Over a year ago

There is no need for DATE_FORMAT(). The function FROM_UNIXTIME() takes the format as the 2nd argument.

Strawberry Over a year ago

@forpas - oh, right - good to know; thanks, that's much more elegant!

Collectives™ on Stack Overflow

Converting Unix Timestamp into a Custom Human Readable time using mySQL

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related