Time comparison return unexpected results

Database
Visitor

Time comparison return unexpected results

I'm trying to do a query on a Teradata 16 Express. The query is basically a comparison between a time column and a static time value. I have reproduced what I'm seeing in the following simple query

 

select 1 where cast ( '07:00:00' as time) > cast( '19:00:00' as time);

This returns one row.

 

I though initially the values where being cast as varchar and compared but if I change the second time value to '18:00:00' I get no rows...

 

I am pretty new to Teradata, and don't remember setting anything in terms of time zones or anything. Any ideas what might be causing this result?


Accepted Solutions
Teradata Employee

Re: Time comparison return unexpected results

You are on the right track; this happens because the comparison is done using UTC / GMT values.

 

HELP SESSION will show you the session time zone offset (though that offset may include the "daylight saving" adjustment, which is not included when dealing with TIME data type). The dbscontrol utility will show you the system level setting. You can also override the system level setting at the user level or session level.

 

My copy of TD Express 16.00 came set to "America Eastern" in tdlocaledef / dbscontrol so the TIME offset is -05:00 (and TIMESTAMP offset would be -05:00 or -04:00 depending on the date portion).

07:00:00 EST = 12:00:00 GMT

19:00:00 EST = 00:00:00 GMT

18:00:00 EST = 23:00:00 GMT

1 ACCEPTED SOLUTION
3 REPLIES
Teradata Employee

Re: Time comparison return unexpected results

I get the same results with Teradata 16.10 in the UTC-8 time zone.  Also if I change 19:00 to 20:00 or 23:59:59.  Sounds like a bug to me.  However, timestamps give the correct result:

select 1 where cast ( '2018-02-06 07:00:00' as timestamp) > cast( '2018-02-06 19:00:00' as timestamp);
 *** Query completed. No rows found.

select 1 where cast ( '2018-02-06 07:00:00' as timestamp) > cast( '2018-02-05 19:00:00' as timestamp);
 *** Query completed. One row found. One column returned.
   1

Teradata Employee

Re: Time comparison return unexpected results

You are on the right track; this happens because the comparison is done using UTC / GMT values.

 

HELP SESSION will show you the session time zone offset (though that offset may include the "daylight saving" adjustment, which is not included when dealing with TIME data type). The dbscontrol utility will show you the system level setting. You can also override the system level setting at the user level or session level.

 

My copy of TD Express 16.00 came set to "America Eastern" in tdlocaledef / dbscontrol so the TIME offset is -05:00 (and TIMESTAMP offset would be -05:00 or -04:00 depending on the date portion).

07:00:00 EST = 12:00:00 GMT

19:00:00 EST = 00:00:00 GMT

18:00:00 EST = 23:00:00 GMT

Junior Contributor

Re: Time comparison return unexpected results

This is not a bug, it's how TIME is implemented.

A TIME (without time zone) is stored in UTC, i.e. normalized when inserted.

SELECT Cast ( '07:00:00' AS TIME) AT 0, Cast( '19:00:00' AS TIME) AT 0;
-- or the same using Standard SQL literals
SELECT TIME '07:00:00' AT 0, TIME '19:00:00'  AT 0;

07:00:00 AT TIME ZONE 0  19:00:00 AT TIME ZONE 0
-----------------------  -----------------------
         21:00:00+00:00           09:00:00+00:00

 

 

CREATE VOLATILE TABLE vt ( i INT, t TIME(0), tz TIME(0) WITH TIME Zone) ON COMMIT PRESERVE ROWS;

SET TIME Zone 10;
-- inserting 24 hours
INSERT INTO vt
SELECT day_of_calendar -1 AS i,
   TIME '00:00:00' + (INTERVAL '1' HOUR * i),
   TIME '00:00:00+00:00' + (INTERVAL '1' HOUR * i)
FROM sys_calendar.CALENDAR 
WHERE i BETWEEN 0 AND 23;

SELECT i, t, t AT 0, tz
FROM vt
ORDER BY t;

          i         t  t AT TIME ZONE 0              tz
-----------  --------  ----------------  --------------
         10  00:00:00    00:00:00+00:00  10:00:00+00:00
         11  01:00:00    01:00:00+00:00  11:00:00+00:00
         12  02:00:00    02:00:00+00:00  12:00:00+00:00
         13  03:00:00    03:00:00+00:00  13:00:00+00:00
         14  04:00:00    04:00:00+00:00  14:00:00+00:00
         15  05:00:00    05:00:00+00:00  15:00:00+00:00
         16  06:00:00    06:00:00+00:00  16:00:00+00:00
         17  07:00:00    07:00:00+00:00  17:00:00+00:00
         18  08:00:00    08:00:00+00:00  18:00:00+00:00
         19  09:00:00    09:00:00+00:00  19:00:00+00:00
         20  10:00:00    10:00:00+00:00  20:00:00+00:00
         21  11:00:00    11:00:00+00:00  21:00:00+00:00
         22  12:00:00    12:00:00+00:00  22:00:00+00:00
         23  13:00:00    13:00:00+00:00  23:00:00+00:00
          0  14:00:00    14:00:00+00:00  00:00:00+00:00
          1  15:00:00    15:00:00+00:00  01:00:00+00:00
          2  16:00:00    16:00:00+00:00  02:00:00+00:00
          3  17:00:00    17:00:00+00:00  03:00:00+00:00
          4  18:00:00    18:00:00+00:00  04:00:00+00:00
          5  19:00:00    19:00:00+00:00  05:00:00+00:00
          6  20:00:00    20:00:00+00:00  06:00:00+00:00
          7  21:00:00    21:00:00+00:00  07:00:00+00:00
          8  22:00:00    22:00:00+00:00  08:00:00+00:00
          9  23:00:00    23:00:00+00:00  09:00:00+00:00