将毫秒传递给存储过程 mySQL [英] Passing milliseconds into stored procedure mySQL

问题描述

我的存储过程发生了奇怪的事情.我正在将一个 int 传递给存储过程 - 当我将值作为存储过程工作的纪元时间传递时以毫秒为单位传递与 int 相同的值它不起作用

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

将 1360292312 (int) 传入 FROM_UNIXTIME(fromDate) 有效(给出正确的翻译)

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

将 1360292312000 (int) 传入 FROM_UNIXTIME(fromDate/1000) 不起作用(没有给我我期望的)

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

为什么 - 我需要将类型更改为 bigINT 吗?

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

推荐答案

这是 signed int 最大值的问题.在将数字发送到 FROM_UNIXTIME() 之前，您必须将数字除以 1000.更重要的是，它应该小于或等于signed int的最大值.

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 最多只能达到 2147483647 并且从我看到的 FROM_UNIXTIME 工作或任何值到此为止.如果你通过 2147483647+1 它返回 null.

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.

查询和结果 -

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)

参见 fiddle 示例

整数类型手册

FROM_UNIXTIME 手册

FROM_UNIXTIME manual

这篇关于将毫秒传递给存储过程 mySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！