Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

  • DATEDIFF(d,t)
  • TIMEDIFF(d,t)
  • 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 US/Central and 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:

TIMEDIFF(CONVERT_TZ(t),'US/Central','UTC'),d)

Even over a time zone change (from CST to CDT) it seems to work...

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

There is a manual page on Time and Date functions.

The best way to compare date and time and get minutes back is probably to use one of the following:

SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60
SELECT TIMESTAMPDIFF(MINUTE,timeb,timea)

Note that while TIMEDIFF works with times, TIMESTAMPDIFF requires a date component - and the values are switched around for TIMESTAMPDIFF.

You can return hours with either of these:

SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60/60
SELECT TIMESTAMPDIFF(HOUR,timeb,timea)

Note that when using DATEDIFF, the value returned is a difference between the date components of the given time - thus, a value of 23:59 on one day compared to a value of 0:01 on the next (a 2 minute difference) results in a date difference of 1 (a full day).

To get a difference between two times in 24-hour periods (length of a day) first calculate the difference in minutes then divide by the proper values to find the number of 24-hour periods. Try this:

SELECT TRUNCATE(TIMESTAMPDIFF(MINUTE,timeb,timea)/60.0/24.0,0)

This truncates the decimal portion, returning only the number of complete 24-hour periods in the specified timespan.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.