converting to UTC from possibly different timezone

Database
Enthusiast

converting to UTC from possibly different timezone

Hello all,

 

I am working with four different timestamps that are created as below columns in different databases:

1. Table 1

Action01_Dttm_PST TIMESTAMP(6)

Ex. 7/19/2017 12:39:46.673000

2. Table 2

 Action02_dttm TIMESTAMP(6) WITH TIME ZONE

Ex. 3/25/2018 17:47:21.547000-05:00

3. Table 3

Action03_Dttm TIMESTAMP(6) WITH TIME ZONE NOT NULL

Ex. 6/19/2013 08:48:07.000000-05:00

4. Coming from a MySQL table with system timezone 'UTC'. I am storing this in TD as timestamp(0). Should I convert it to some other timestamp format such as timestamp(6) with timezone?

 

I have to combine data in these three tables into a Target table and then sort it using the dttm column. Can you help me to convert these values into uniform 'UTC' representation before I insert them into the Target table?

 

Thanks in advance,

Nik

 

 

 

 

 


Accepted Solutions
Teradata Employee

Re: converting to UTC from possibly different timezone

For #4: Yes, it's probably best to make the column TIMESTAMP(n) WITH TIME ZONE (though fractional seconds may be unnecessary) and load as UTC. You could either append time zone to the character representation, or explicitly SET TIME ZONE for the load session, or set the default time zone for the ETL user doing the load. Note that Teradata currently recognizes hour/minute time zone offsets and 'GMT' but not 'UTC'.

 

I'm guessing that values in Table #1 use your TD system "local" time zone? Then you can use the AT 'GMT' clause for all four sources to INSERT to the target table.

 

But note that sorting will always be based on equivalent GMT values, regardless of which time zone is stored or implied.

 

 

 

 

1 ACCEPTED SOLUTION
3 REPLIES
Teradata Employee

Re: converting to UTC from possibly different timezone

If you load all the timestamps as timestamp-with-time-zone, then they will sort correclty. You do not have to convert them to UTC becuase Teradata by default stores them internally as UTC - that makes them easy to sort.  See the SQL Data Types manual on time zones, and the SQL DML manual on the Select Statement, Order By Clause / Unexpected Sort Order.

Enthusiast

Re: converting to UTC from possibly different timezone

Hi @GJColeman

Thank you for the reply. I forgot to mention that 

Action01_Dttm_PST

mentioned above is stored in TD originally in PST. Does it mean that it will be converted to and sorted along with others as well? 

Teradata Employee

Re: converting to UTC from possibly different timezone

For #4: Yes, it's probably best to make the column TIMESTAMP(n) WITH TIME ZONE (though fractional seconds may be unnecessary) and load as UTC. You could either append time zone to the character representation, or explicitly SET TIME ZONE for the load session, or set the default time zone for the ETL user doing the load. Note that Teradata currently recognizes hour/minute time zone offsets and 'GMT' but not 'UTC'.

 

I'm guessing that values in Table #1 use your TD system "local" time zone? Then you can use the AT 'GMT' clause for all four sources to INSERT to the target table.

 

But note that sorting will always be based on equivalent GMT values, regardless of which time zone is stored or implied.