在旧版MySQL(< 5.5.0)中模拟TO_SECONDS() [英] Emulating TO_SECONDS() in older versions of MySQL (<5.5.0)

问题描述

出于性能和简便性的原因，我想以秒(或任何数字类型，以秒为单位)获取MySQL 3.x服务器中DATETIME列的内容-我只是想避免在使用UNIX_TIMESTAMP( )[我表中的日期确实来自不同的地区，所以我宁愿对某些时区补偿的怪异现象是否还在我身后也没有疑问]).

For performance and simplicity reasons I would like to get the contents of a DATETIME column in my MySQL 3.x server as seconds (or any numeric type really - I just want to avoid all the apparent nastiness of timezones when using UNIX_TIMESTAMP() [the dates in my table are indeed from different locales so I'd rather not have any doubt as to whether or not some timezone-compensation weirdness is going on behind my back]).

TO_SECONDS()函数似乎很理想，直到我发现它仅适用于较新的MySQL安装(升级不是一种选择)...

The TO_SECONDS() function seemed ideal, until I found out that it only works on newer MySQL installations (upgrading is not an option)...

我虽然正在做这样的事情:

I though about doing something like this:

SELECT (TO_DAYS(Timestamp)-730486)*86400+TIME_TO_SEC(Timestamp)

要手动计算自2000年1月1日以来经过的秒数.但是，通过强制服务器创建临时表或类似内容，可能会给服务器带来不必要的负担?

To manually calculate the number of seconds elapsed since 2000-01-01. But it seems like it might put unnecessary load on the server by forcing it to make a temporary table or something?

我也可以这样做:

SELECT TO_DAYS(Timestamp), TIME_TO_SEC(Timestamp)

并自己组合结果，但这在代码方面使其变得不那么简单.

and combine the results myself, but that makes it less simple on the code-side.

最好的折衷办法是什么?我将为每个查询获取大量的行(大约10 ^ 6)，因此客户端和服务器端的性能都不是完全不重要的.

What's the best compromise? I'll be fetching a large number of rows (on the order of 10^6) each query, so both client and server-side performance is not entirely unimportant..

谢谢.

(帖子经过编辑，以减少混乱)

(post edited to reduce confusion)

推荐答案

首先，为了确保新字段为BIGINT ...正确?

Firstly, just to make sure, the new field will be a BIGINT... correct?

可以使用显式强制转换来防止溢出吗?

Can you use explicit casting in to prevent the overflow?

SELECT CAST(TO_DAYS(Timestamp)*86400 + TIME_TO_SEC(Timestamp) AS UNSIGNED INTEGER)

或者在填充新的BIGINT字段之前使用中间字符串吗?

Or perhaps use an intermediate string before populating the new BIGINT field?

SELECT CAST(TO_DAYS(Timestamp)*86400 + TIME_TO_SEC(Timestamp) AS UNSIGNED CHAR(11))

这篇关于在旧版MySQL(< 5.5.0)中模拟TO_SECONDS()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！