CODEWITHSUNDEEP
mysqldatetime
Saffik
Saffik

Reputation: 1013

Converting Unix Timestamp into a Custom Human Readable time using mySQL

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

Upvotes: 1

Views: 106

Answers (2)

Strawberry
Strawberry

Reputation: 33935

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 |
+---------------------+

Upvotes: 1

forpas
forpas

Reputation: 164204

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.

Upvotes: 1

Related Questions