difference between two times - strange behaviour

Database

difference between two times - strange behaviour

I'm trying to find elapsed time between two times. When subtract earlier time from the later one, everything works fine unless the times are on the opposite sides of 10:00 AM, in which case DBMS seems to swap the two times and return negative interval. The server is in GMT+10 time zone, so I have a feeling it has something to do with that, but anything I've tried so far did not help (converting times to GMT, or to GMT+10 explisitly, etc.). Can anyone help? Example code and screenshot below.

 

SELECT
    CAST('09:00:00' AS TIME FORMAT 'HH:MI:SS') - CAST('08:00:00' AS TIME FORMAT 'HH:MI:SS') HOUR TO SECOND AS intv_08_09,
    CAST('10:00:00' AS TIME FORMAT 'HH:MI:SS') - CAST('09:00:00' AS TIME FORMAT 'HH:MI:SS') HOUR TO SECOND AS intv_09_10,
    CAST('11:00:00' AS TIME FORMAT 'HH:MI:SS') - CAST('10:00:00' AS TIME FORMAT 'HH:MI:SS') HOUR TO SECOND AS intv_10_11;

Teradata time difference issue.png

 

 

Tags (3)

Accepted Solutions
Highlighted
Junior Contributor

Re: difference between two times - strange behaviour

See this thread :-)

 

Is your source column a TIME or a TIME WITH TIME ZONE? 

 

Play around with specifying time literals with time zone, e.g. TIME '10:00:00+00:00' to get the correct UTC-based value.

1 ACCEPTED SOLUTION
2 REPLIES
Teradata Employee

Re: difference between two times - strange behaviour

You are correct. The calculation is done after converting both times to GMT. Best practice is to use timestamps to calculate elapsed times, not TIME data types.

11:00:00 at GMT+10 = 01:00:00 GMT

10:00:00 at GMT+10 = 00:00:00 GMT

09:00:00 at GMT+10 = 23:00:00 GMT

08:00:00 at GMT+10 = 22:00:00 GMT

 

Highlighted
Junior Contributor

Re: difference between two times - strange behaviour

See this thread :-)

 

Is your source column a TIME or a TIME WITH TIME ZONE? 

 

Play around with specifying time literals with time zone, e.g. TIME '10:00:00+00:00' to get the correct UTC-based value.