I'm trying to understand how to compare a DATETIME value (ostensibly inserted as GMT) and a TIMESTAMP value (automatically generated at INSERT time).
As I understand it, the DATETIME is set to whatever it is set to - and the reporting comes back with the same value, no matter what time zones are in place on the server or client.
In the MySQL documentation, it sounds like TIMESTAMP values are reported in the current timezone, but stored in UTC.
In this situation then, subtracting the DATETIME value (earlier event) from the TIMESTAMP value should be possible without a lot of timezone conversion - because the DATETIME is GMT and the TIMESTAMP value is UTC. However, doing this accurately - and understanding it - is proving to be a problem.
I have situations where I need
(DATETIME - TIMESTAMP) in minutes and in days. If we assume that column
d is DATETIME and column
t is TIMESTAMP... So far, I've tried:
UNIX_TIMESTAMP(d) - UNIX_TIMESTAMP(t) - 1800(accounting for DST)
Our time zone here is US Central Time - currently Central Daylight Time - GMT-6 for CST and GMT-5 for current CDT.
It also looks like DATEDIFF converts both values into dates then compares them - instead of comparing time between the values and dividing by the number of 24 hours in each. Thus, I don't think DATEDIFF will work directly for me anyway.
The specific questions, then are:
- How do you compare a DATETIME in GMT with a TIMESTAMP value? Specifically, finding the number of minutes between the two, assuming the TIMESTAMP value was generated last.
- What's the best way to get the difference in 24 hour slots between the same two values? I assume that it would be - using A1 as the answer to the above question -
TRUNCATE(A1 / (24.0 * 60.0))...
UPDATE: Tried using the
CONVERT_TZ function with the values of
GMT. However, the
CONVERT_TZ function with such specifications appears to read the setting of Daylight Saving Time from the current system time and not from the time as read from the database field. This means that when the data should be taken as a date in CST, it is read as CDT instead.
I keep thinking it's got to be simple to get the UTC representation of a
TIMESTAMP field - which would simplify things, seems to me.
UPDATE 2: Looks to me like I mispoke regarding
CONVERT_TZ. I'm using this function and it seems to work:
Even over a time zone change (from CST to CDT) it seems to work...