Any effects of timezone on data

Database

Any effects of timezone on data

Greetings Experts,

I need a clarification on how the different timezone or relative settings effect the users on server and clients.

Say, a user from Australia inserted into a table with 2 cols (col_name, col_timestamp) through client like sql assistant

insert into abc values ('sachin','2015-08-15 06:54:12:123456')

and the server is located in US.  I guess that the timestamp is changed according to the timezone of the US (server time which may result in the time_stamp value as 2015-08-14 17:54:12:123456' assuming 13 hours deviation).

If a user queries (once with Australia timezone and then with another timezone say London)

select * from abc where col_timestamp='2015-08-15 20:54:12:123456'

Will there be any differences in the result (specifically when the server and the client timezones have different dates at the same time)  Does the user get the above row in each case irrespective of the any settings that affects.

How is a TD system configured to be globally used all over the world with many users querying in different geographies.

Tags (1)
2 REPLIES
Teradata Employee

Re: Any effects of timezone on data

Teradata has no way to actually know the client's time zone, so one of the attributes of every session is a time zone offset. It can be modified within the session (using SET SESSION TIME ZONE), defaulted at the user level, or defaulted at the system level. Input timestamp values without an explicit time zone offset are interpreted based on the session time zone, and by default output timestamp values are displayed relative to session time zone; SQL can also request external value of an expression be "AT" some other time zone.

It's important that the data loads identify the correct time zone of the data being loaded. This can be done either by controlling the session time zone or by including the time zone offset within the data field itself (TIMESTAMP WITH TIME ZONE). Data is then converted to a "normalized" internal time zone for storage (generally UTC). Comparisons are based on internal UTC values.

Teradata Employee

Re: Any effects of timezone on data

If there is a need to see data not only relative to the client's time zone but also "AT SOURCE", then consider storing the input time zone information as well, e.g. TIMESTAMP WITH TIME ZONE.