Convert unix timestamp to human readable time in mysql

Question

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?

The easiest solution is probably to implement it yourself with modulus, division and rounding. — Tim
– Tim, Commented Dec 4, 2012 at 9:23

Mayank Sharma · Accepted Answer · 2017-01-02 07:22:02Z

2

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

answered Jan 2, 2017 at 7:22

Mayank Sharma

838 bronze badges

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

Comments

Sander_V · Accepted Answer · 2013-06-04 21:34:42Z

1

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

answered Jun 4, 2013 at 21:34

Sander_V

312 bronze badges

Collectives™ on Stack Overflow

Convert unix timestamp to human readable time in mysql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related