Balmipour Balmipour - 1 year ago 115
MySQL Question

FROM_UNIXTIME returning one extra hour

I got an extremely simple need :
convert some milliseconds values to "##:##:##" (hours/minutes/seconds) format.

It appears FROM_UNIXTIME can do the job, so I used it, but...

SELECT
FROM_UNIXTIME(42, "%H:%i:%s") AS pony
,FROM_UNIXTIME(3702, "%H:%i:%s") AS unicorn


returns

pony unicorn
01:00:42 02:01:42


With no timezone setting in this function, I don't know how to remove this extra (wtf) hour.

removing 3600 seconds produces a negative value, which outputs a NULL

I guess I should use another way, but if I have to use half a dozen converts, I could as well do it manually... is there nothing simple and efficient ?




I don't have an exact MySQL version, but I need a minimum of portability anyway. Something working with, say MySQL 5.x
Do I need to make my calculations manually ?





EDIT :
Opted for an ugly, but working "solution" :

CONCAT(
LPAD(FLOOR((my_column/1000) / 3600), 2, '0'), ':',
LPAD(FLOOR(((my_column/1000) % 3600) / 60), 2, '0'), ':',
LPAD(FLOOR((my_column/1000) % 60), 2, '0')) AS time


Outputs with desired format. examples "03:12:04" or "123:00:44"

Answer Source

I had to opt for an ugly, but working "solution" :

CONCAT(
  LPAD(FLOOR((my_column/1000) / 3600), 2, '0'), ':',
  LPAD(FLOOR(((my_column/1000) % 3600) / 60), 2, '0'), ':',
  LPAD(FLOOR((my_column/1000) % 60), 2, '0')) AS time

Outputs with desired format. examples "03:12:04" or "123:00:44"

C14L's comment should be a better choice, but it only made things worse :/

Please, post an answer if you have something reliable and easier to use.
Having to making such calculations manually just doesn't make sense to me.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download