Inserting in a TIMESTAMP
with TIME ZONE column:
When we insert row in the table with TIMESTAMP WITH TIME
ZONE column, it is
not necessary to specify the time zone as the part of timestamp. The current time zone for the session will be stored with
the timestamp with default (thus Time Zone
is stored Implicitly).
Say we
create a table as below :
CREATE VOLATILE TABLE TEST
(
tstamp
TIMESTAMP WITH TIME ZONE
) ON COMMIT
PRESERVE ROWS;
INSERT INTO TEST VALUES (TIMESTAMP '2013-01-01 12:34:56.123456');
--> note that we did not specify the time
zone when inserting the timestamp. It will
pick up the Timezone from the current setting which is system level 00:00
SET TIME ZONE INTERVAL '05:00' HOUR TO MINUTE ;/*
explicit session level setting */
INSERT INTO TEST VALUES (TIMESTAMP
'2013-01-01 12:34:56.123457'); --> Here
again we don’t specify the time zone when inserting the timestamp. It will pick up the time zone from the current setting
which is the 05:00 setting made explicitly for this session
select CAST(tstamp AS CHAR(35)) from test ;
tstamp
2013-01-01 12:34:56.123456+00:00
2013-01-01 12:34:56.123457+05:00
From
the result we can see that zone is defaulted from the current time zone
setting.
When
your system uses a TIME ZONE of 00:00 we can think as if your system is located
at GMT.
When
we change the TIME ZONE we can think that your system moved to a different
location which follows the new time zone.
Casting TIMESTAMP with TIME
ZONE TO TIMESTAMP column (Time Zone Normalization):
We can
cast TIMESTAMP WITH TIME ZONE to TIMESTAMP.
This
conversion does not drop the TIME ZONE, instead it incorporates the time zone
in the timestamp to give us an absolute value. This process is called as Zone
Normalization.
The result of normalization is always relative to the current
TimeZone setting. (The current setting could come from System level setting Or
user level setting OR session level setting)
Example:
SET TIME ZONE LOCAL; --> Set
the time Zone to 00:00
select CAST(tstamp AS
timestamp(6)) from test --> 1/1/2013
12:34:56.123457
Relative
to GMT the time is 12:34
SET TIME ZONE INTERVAL '05:00'
HOUR TO MINUTE ;/* explicit session level setting */
Here now we change the current
time zone to Pakistan's time zone
select CAST(tstamp AS
timestamp(6)) from test --> 1/1/2013 17:34:56.123457
Now here we get the relative to Pakistan's timezone. We can see that 5 hours are added to 12:34.
What
we are seeing here is that 12:34 in GMT is actually 17:34 is Pakistan.
This is how normalization works.-- Relative to current
time zone setting.
Inserting records with explicit time zone.
Earlier
we saw that we can insert timestamp fields without writing the time zone
explicitly. The system will populate the time zone value from the current time
zone setting. The current time zone setting can come from system level setting
/ user level setting or the session level setting.
However we can also insert records with
explicit time zone as shown below.
insert into test values
(TIMESTAMP'2013-01-01 12:11:11+06:00');
The
Time zone +6:00 is followed by Bangladesh.
We are
actually inserting time 12:11:11 in Bangladesh.
Now
when we normalize we get the following result:
select CAST(tstamp AS
timestamp(6)) from test --> 1/1/2013 06:11:11.000000
We see
the time as 06:11:11. What we are seeing currently is the time at GMT ( because
my system level setting is 00:00 .i.e GMT) when the time in Bangladesh is
12:11:11
Now I
change my session level time zone setting to US CST
SET TIME ZONE INTERVAL -'06:00' HOUR TO MINUTE ;/*
explicit session level setting */
And
when I see the normalized time zone following is what we get
select CAST(tstamp AS TIMESTAMP(6)) from test ;
--> 1/1/2013 00:11:11.000000
Note
that the time we are seeing is actually the time in Texas when time in
bangladesh is 12:11:11 of the same day.
Now I
change the TIME ZONE to Hawaii.
SET TIME ZONE INTERVAL -'11:00' HOUR TO MINUTE ;/*
explicit session level setting */
select
CAST(tstamp AS TIMESTAMP(6)) from test ; --> 12/31/2012 19:11:11.000000
Note
that when its 12:11:11 of 1st Jan 2013 , its 19:11:11 of 31st Dec 2012 in
hawaii.
Note
that it actually put us on the previous day.
AT LOCAL option:
AT
LOCAL is another technique of normalizing the time zones.
Using
AT LOCAL has the same effect as casting we performed earlier.
SET TIME ZONE LOCAL; --> Sets the Time Zone to
system level setting which is 00:00 on my system.
select CAST(tstamp AS TIMESTAMP(6)) from test ;
--> 1/1/2013 06:11:11.000000
SET TIME ZONE INTERVAL -'11:00' HOUR TO MINUTE ;/*
explicit session level setting to hawaii*/
Select tstamp AT LOCAL FROM test; -->12/31/2012
19:11:11.000000
select CAST(tstamp AS TIMESTAMP(6)) from test ;
--> 12/31/2012 19:11:11.000000
We can
see the result of casting and using AT LOCAL is exactly same.
