Passing milliseconds into stored procedure mySQL

Question

I have a strange something going on with my stored proc. I am passing an int into the stored proc - when i pass as the value as a epoch time the stored procedure works passing that same value as the int in milliseconds it DOES not work

passing 1360292312 (int) into FROM_UNIXTIME(fromDate) works (give the correct translation)

passing 1360292312000 (int) into FROM_UNIXTIME(fromDate/1000) does not work (not giving me what i expect)

why is that - do i need to change the type to bigINT?

Answer 1

This is a problem with signed int max value. You must divide the number by 1000 before sending it to FROM_UNIXTIME(). More importantly, it should be less than or equal to the max value of signed int.

signed int can only go up to 2147483647 and from what I see FROM_UNIXTIME works or any value up to that. If you pass 2147483647+1 it returns null.

Query and the results -

SELECT FROM_UNIXTIME(1360292312), #February, 08 2013 02:58:32+0000
        FROM_UNIXTIME(1360292312000),  #(null)
        FROM_UNIXTIME(1360292312000/1000), #February, 08 2013 02:58:32+0000
        FROM_UNIXTIME(2147483647), #January, 19 2038 03:14:07+0000
        FROM_UNIXTIME(2147483648), #(null)
        FROM_UNIXTIME(4294967295) #(null)

see fiddle examples

Integer type manual

FROM_UNIXTIME manual