Emulating TO_SECONDS() in older versions of MySQL (<5.5.0)
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]).
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)
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?
I could also just do:
SELECT TO_DAYS(Timestamp), TIME_TO_SEC(Timestamp)
and combine the results myself, but that makes it less simp开发者_如何学Gole on the code-side.
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..
Thanks,.
(post edited to reduce confusion)
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)
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))
精彩评论