Elegant way of adding time to a date in non-GMT time zones

Database
Highlighted

Elegant way of adding time to a date in non-GMT time zones

Teradata stores TIME data types in UTC (GMT) time zone, so if DBMS server is in any other time zone, calculations with times produce unexpected results, as in these examples:

https://community.teradata.com/t5/Database/Time-comparison-return-unexpected-results/m-p/78255/highl...

https://community.teradata.com/t5/Database/difference-between-two-times-strange-behaviour/m-p/78221

 

One way to deal with this is to cast time back-and-forth and force it into GMT time zone, which works but is very clunky. Does anyone know a more elegant way to do it?

 

Teradata time difference solution.png

 

The code:

    -- DBMS server is in GMT+10 time zone
SELECT
    CAST('01-Jan-2018' AS DATE FORMAT 'DD-MMM-YYYY')   AS dt, -- make a date
    CAST('10:00:00' AS TIME FORMAT 'HH:MI:SS')         AS tm10, -- make time, 10 hours
    
    -- add time to date to get the timestamp (date & time)
    -- attempt 1: as-is - result is NOT what we expect
    CAST(dt AS TIMESTAMP(0)) + (tm10 - TIME '00:00:00' HOUR TO SECOND) AS dtm10,
    
    -- attempt 2: forcing to GMT time zone - works, but clunky
    CAST(dt AS TIMESTAMP(0)) 
      + (CAST( CAST(tm10 as CHAR(8)) || '+00:00' AS TIME WITH TIME ZONE) 
          - TIME '00:00:00+00:00' HOUR TO SECOND)                      AS dtm10_gmt

 

 

 


Accepted Solutions
Junior Contributor

Re: Elegant way of adding time to a date in non-GMT time zones

 

SELECT
   DATE '2018-01-01' AS dt, -- make a date
   TIME '10:00:00'   AS tm10, -- make time, 10 hours
   -- Cast the TIME -> string -> INTERVAL
   Cast(dt AS TIMESTAMP(0)) + Cast(Cast(tm10 AS CHAR(8)) AS INTERVAL HOUR TO SECOND)

 

Of course, you can put those calculations in a SQL-UDF and nobody has to know/think about it anymore

REPLACE FUNCTION DateTime2Timestamp (d DATE,t TIME(0))
RETURNS TIMESTAMP(0)
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN 
   Cast(d AS TIMESTAMP(0)) + 
   +Cast(Cast(t AS CHAR(8)) AS INTERVAL HOUR TO SECOND)

 

 

 

 

 

1 ACCEPTED SOLUTION
3 REPLIES
Junior Contributor

Re: Elegant way of adding time to a date in non-GMT time zones

 

SELECT
   DATE '2018-01-01' AS dt, -- make a date
   TIME '10:00:00'   AS tm10, -- make time, 10 hours
   -- Cast the TIME -> string -> INTERVAL
   Cast(dt AS TIMESTAMP(0)) + Cast(Cast(tm10 AS CHAR(8)) AS INTERVAL HOUR TO SECOND)

 

Of course, you can put those calculations in a SQL-UDF and nobody has to know/think about it anymore

REPLACE FUNCTION DateTime2Timestamp (d DATE,t TIME(0))
RETURNS TIMESTAMP(0)
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN 
   Cast(d AS TIMESTAMP(0)) + 
   +Cast(Cast(t AS CHAR(8)) AS INTERVAL HOUR TO SECOND)

 

 

 

 

 

Re: Elegant way of adding time to a date in non-GMT time zones

Thank you, that worked as expected.

UDF would be handy, but I won't use it yet, as it will create dependency on other user's access to the function and some other concerns in our evironment.

Junior Contributor

Re: Elegant way of adding time to a date in non-GMT time zones

UDFs for public access are usually placed in a either specific UDF database or in SYSLIB with PUBLIC access rights.

When it's in SYSLIB there's no need to fully qualify it because it's in the perser's search path.