mysql - mysql 出现超出范围错误

Question

我试图使用mysql实现黑客新闻算法并得到以下错误

    Double value is out of range in 
    pow(((unix_timestamp() - unix_timestamp(postDateTime))/3600 + 1),1.5)

Where the value of postDateTime is 2012-12-15 10:41:31

如果我将上述实现为

pow(((unix_timestamp() - unix_timestamp(cast(postDateTime as signed)))/3600 + 1),1.5)

它工作完美，但我不明白其背后的原因

Answer 1

您运行的 mysql 版本是什么？检查this link出来。

并检查the documentation了解为什么您会得到负值:

If called with no argument, returns a Unix timestamp (seconds since '1970-01-01 00:00:00' UTC) as an unsigned integer. If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since '1970-01-01 00:00:00' UTC. date may be a DATE string, a DATETIME string, a TIMESTAMP, or a number in the format YYMMDD or YYYYMMDD. The server interprets date as a value in the current time zone and converts it to an internal value in UTC.

...

If you want to subtract UNIX_TIMESTAMP() columns, you might want to cast the result to signed integers. See Section 12.10, “Cast Functions and Operators”.