CODEWITHSUNDEEP
mysql
mflammia
mflammia

Reputation: 87

Convert MySQL Unix Time Stamp to Human Readble Form

Appreciate that this topic has been covered many times and I have tried all the combinations I can find without success.

The following timestamp is an example of that returned when using rpt_default_day.time_stamp:

1474502400000

If I put this time stamp into the following website it returns the correct date and time:

http://www.epochconverter.com/

Below are some examples of queries I have been using:

DATE_FORMAT(FROM_UNIXTIME('rpt_default_day.time_stamp'), '%e %b %Y') AS 'Date',
FROM_UNIXTIME('rpt_default_day.time_stamp') AS 'Date', 
FROM_UNIXTIME(UNIX_TIMESTAMP('rpt_default_day.time_stamp')) AS 'Date',

Problem is whatever I do I'm always getting returned the epoch time of:

'1970-01-01 01:00:00.000000'

Appreciate any help in advance.

Upvotes: 0

Views: 66

Answers (2)

yvs
yvs

Reputation: 517

A proper format values should be passed as a parameter to "FROM_UNIXTIME" function :

mysql> SELECT UNIX_TIMESTAMP(NOW()) as ts, 
FROM_UNIXTIME(UNIX_TIMESTAMP(NOW()), '%Y %D %M %h:%i:%s %x') as f_tm;
+------------+-----------------------------------+
| ts         | f_tm                              |
+------------+-----------------------------------+
| 1474755927 | 2016 25th September 01:25:27 2016 |
+------------+-----------------------------------+
1 row in set (0.06 sec)

possible format values are specified in the Mysql Documentation

Upvotes: 0

Kate
Kate

Reputation: 1836

Remove three zeros from your string and you're good to go.

Upvotes: 2

Related Questions