We have a requirement where we get timestamp data for any time zone. But when we store it in the database it must be in GMT.
So the following change has been made :
For all the columns that need to be maintained in GMT format
we create it as the following:
CREATE table temp
(
TEST TIMESTAMP WITH LOCAL TIME ZONE
);
Also the database has the DBTIMEZONE as 00:00. This means that the Database Time zone is GMT and any time stamp data with the timezone information will be stored in GMT timezone.
Update:
Some more observations as I working on this today as well:
1. After you insert the data, if you use SQL Plus to read back the data, it will display it in your local timezone. Because that is how the data will be made available to it. However if you login directly to the
database machine, you will see the data in the timezone you wanted.
2. This is more weird than (1). If you change the timezone on your machine to the required timezone, and then insert the data, exit and start a new session, and then view the data, it retains the timezone!! . It does not convert it to the timezone on your machine.
3. Writing a procedure does not help as it takes the timezone of the client machine when inserting data.
Will keep updating this post as and when I find something
Technorama 