GMT to EDT conversion in Teradata

Database

GMT to EDT conversion in Teradata

Hi,
I am trying to replicate the following ORACLE syntax to Teradata but facing issues in conversion.
can anyone pls help me out?

(substr(to_char(new_time(to_date(inventory.gmt_start_time,'mm/dd/yyyy hh:mi:ss AM'), 'GMT', 'EDT'),'mm/dd/yyyy hh:mi:ss AM'), 1, 14)
|| Case
when to_number(substr(to_char(new_time(to_date(inventory.gmt_start_time,'mm/dd/yyyy hh:mi:ss AM'), 'GMT', 'EDT'),'mm/dd/yyyy hh:mi:ss AM'), 15, 2)) >= 30 then
'30:00'
else
'00:00'
end || substr(to_char(new_time(to_date(inventory.gmt_start_time,'mm/dd/yyyy hh:mi:ss AM'), 'GMT', 'EDT'),'mm/dd/yyyy hh:mi:ss AM'),
20, 3)) half_hr_time

Thanks in Advance.
3 REPLIES
Senior Apprentice

Re: GMT to EDT conversion in Teradata

inventory.gmt_start_time is a string with a GMT=UTC timestamp and must be converted to EDT (= -4 hours)?
And minutes should be truncated to half hours?

string -> timestamp: cast(strcol as timestamp(0) format 'mm/dd/yyyyBhh:mi:ssBt')

GMT -> EDT: tscol - interval '4' hours

minutes from timestamp: extract(minute from tscol)

Good luck

Dieter
Enthusiast

Re: GMT to EDT conversion in Teradata

One small correction, it's "hour" no "s" at the end:
- INTERVAL '4' HOUR

Re: GMT to EDT conversion in Teradata

If you have to take into account dailight savings time you should use the following syntax:

Found in TD 14.0 docu (SQL Functions, Operators, Expressions, and Predicates; Chapter 7: DateTime and Interval Functions and Expressions; "Strings that follow different DST and standard time zone displacements").

Tested on TD 13.10

select
 cast('2013-04-21 10:00:00' as timestamp(0)) as utc_summer
,utc_summer at time zone 'America Eastern' as edt_summer_time
,cast('2013-12-21 10:00:00' as timestamp(0)) as utc_winter
,utc_winter at time zone 'America Eastern' as edt_winter_time
;

Result:
utc_summer  21.04.2013 10:00:00
edt_summer_time 21.04.2013 06:00:00
utc_winter  21.12.2013 10:00:00
edt_winter_time 21.12.2013 05:00:00