Creating a function using select or with statement

Database

Creating a function using select or with statement

I am creating a function that takes an input timestamp and converts it to UTC time by adding/subtracting hours using interval based on the timezone offset shown in the "timestamp with time zone" field. 

 

I have a query that does this successfully (based on current_timestamp for reference):

Variation 1: Select statement

select current_timestamp as INPUT_TIME
, case when sub.TZ_OFFSET = ABS(sub.TZ_OFFSET) then current_timestamp + (sub.TZ_OFFSET(INTERVAL HOUR))
when sub.TZ_OFFSET <> ABS(sub.TZ_OFFSET) then current_timestamp - (sub.TZ_OFFSET(INTERVAL HOUR))
end as UTC_TIME
from (select extract (timezone_hour from current_timestamp) as TZ_OFFSET
, current_timestamp as INPUT_TIME
, case when TZ_OFFSET = ABS(TZ_OFFSET) then current_timestamp + (TZ_OFFSET(INTERVAL HOUR))
when TZ_OFFSET <> ABS(TZ_OFFSET) then current_timestamp - (TZ_OFFSET(INTERVAL HOUR))
end as UTC_TIME) sub;

 Variation 2: WITH statement

with myoffset(TZ_OFFSET) as
(select extract (timezone_hour from current_timestamp) as TZ_OFFSET
) 
select current_timestamp as INPUT_TIME
, case when TZ_OFFSET = ABS(TZ_OFFSET) then current_timestamp + (TZ_OFFSET(INTERVAL HOUR))
when TZ_OFFSET <> ABS(TZ_OFFSET) then current_timestamp - (TZ_OFFSET(INTERVAL HOUR))
end as UTC_TIME
from myoffset;

I wish to create a function that will allow you to pass in a time (as opposed to current_timestamp shown above). I've tried it 2 ways:

REPLACE FUNCTION MY_PROCS_DB.USER_SESSION_TO_UTC_TIME(TS_IN TIMESTAMP(6))
RETURNS TIMESTAMP(6)
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN select case when sub.TZ_OFFSET = ABS(sub.TZ_OFFSET) then TS_IN + (sub.TZ_OFFSET(INTERVAL HOUR))
when sub.TZ_OFFSET <> ABS(sub.TZ_OFFSET) then TS_IN - (sub.TZ_OFFSET(INTERVAL HOUR))
end as UTC_TIME
from (select extract (timezone_hour from TS_IN) as TZ_OFFSET
, TS_IN as INPUT_TIME
, case when TZ_OFFSET = ABS(TZ_OFFSET) then TS_IN + (TZ_OFFSET(INTERVAL HOUR))
when TZ_OFFSET <> ABS(TZ_OFFSET) then TS_IN - (TZ_OFFSET(INTERVAL HOUR))
end as UTC_TIME) sub
)
;

I've also tried this using a WITH statement:

REPLACE FUNCTION MY_PROCS_DB.USER_SESSION_TO_UTC_TIME(TS_IN TIMESTAMP(6))
RETURNS TIMESTAMP(6)
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN with myoffset(TZ_OFFSET) as
(select extract (timezone_hour from TS_IN) as TZ_OFFSET
) 
select case when TZ_OFFSET = ABS(TZ_OFFSET) then TS_IN + (TZ_OFFSET(INTERVAL HOUR))
when TZ_OFFSET <> ABS(TZ_OFFSET) then TS_IN - (TZ_OFFSET(INTERVAL HOUR))
end as UTC_TIME
from myoffset
;

Teradata seems to not like the syntax of either of these. Do you know if SELECT statements or WITH statements are allowed and how I might fix this? 

Thank you.

 

Tags (3)
1 REPLY
Highlighted
Teradata Employee

Re: Creating a function using select or with statement

You cannot use DML (select/insert/update/delete) in a SQL function.  You can use SQL functions and CASE statements.  Conceivably you could simplify this greatly by taking out the select and from.  (And why are you adding/subtracting TZ_OFFSET twice?  And don't you just want to add, since the offset can be positive or negative?)  Also, you would need to handle the timezone_minute portion if you need to handle all the time zones.

But I don't think you have to bother with any of that.  This should return UTC (GMT):

 

Select <any-timestamp-with-time-zone> AT 'GMT'

  (See the SQL Functions manual, "ANSI Date Time Expressions")

 

So if you really need to create a function to do this, the function can simply:

  RETURN TS_IN AT 'GMT';