CODEWITHSUNDEEP
unixtimestampunix-timestampepoch
user1875041
user1875041

Reputation: 21

Convert unix timestamp to human readable time in mysql

We are trying to convert unix timestamp to human readable time when running mysql commands. For the unix date we have this working command

select FROM_UNIXTIME(registered) AS "ResolutionDateLine" from tickets

which gives us an readable date like

2012-12-03 09:41:00

But we do also have unix timestamp "seconds" that we need to convert, using the same line as above we get 1970-01-01 01:00:00 but the actual value should be 89 days, 23 hours, 22 minutes and 34 seconds.

Then we tried

select FROM_UNIXTIME(firstresponsetime, "%dd, %Hh, %Im") AS "Response" from tickets

with this result:

01d, 00h, 12m

Does anyone know how to convert this correctly in the mysql command?

Upvotes: 2

Views: 2708

Answers (2)

Mayank Sharma
Mayank Sharma

Reputation: 83

Your will be like 

 select FROM_UNIXTIME(firstresponsetime, '%d-%m-%Y %H:%i:%s') AS Response from tickets

or you can customize it by change second parameter.

for more please check below link:- http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_from-unixtime

Upvotes: 2

Sander_V
Sander_V

Reputation: 21

Use SEC_TO_TIME (http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_sec-to-time) to convert a duration in seconds to a HH:mm:ss notation.

select sec_to_time(3500); results in 00:58:20

Upvotes: 1

Related Questions