Tags: 10g, database, dbtimezone, issued, mysql, offset, oracle, region, setting, sql, stores, systimestamp, time, timezone, zone
setting timezone in 10g systimestamp/dbtimezone
Using DB 10.2 and want to set the database time zone so that SYSTIMESTAMP stores a time zone as a region rather than h:m offset. I've issued the alter database required to set:
SQL> select dbtimezone from dual;
.. and restarted the database, but when I query SYSTIMESTAMP I still get:
SQL> select systimestamp,
2 extract(timezone_region from systimestamp),
3 extract(timezone_abbr from systimestamp) from dual;
20-DEC-07 188.8.131.52000 AM +11:00
The +11:00 setting I imagine is coming from the OS (Windows Server 2003). The concepts manual implies that the DB setting should be used before the OS, but I get the impression from the Admin Guide, that it needs to be set when the DB is created or modified in the OS only.
Can someone explain how I get the timezone region into the SYSTIMESTAMP ?
Leave a comment...
- 4 Comments
- Oracle stores things the way Oracle stores things you can not change that.
What you can do is alter the display.
Focus your attention there when reading the docs at http://tahiti.oracle.com.#1; Sat, 23 Feb 2008 19:25:00 GMT
- systimestamp or sysdate always makes a os call to get system time stamp and showes in system timzone
There is timezone session variable which you will set for each session and database's time zone you set during the creation during the creation of db OR before creating a column timestamp with local time zone.
SQL> select sessiontimezone,dbtimezone from dual;
(I am in eastern and my db in central for this test case)
SQL> select current_Timestamp,systimestamp from dual;
19-DEC-07 08.29.34.862000 PM US/EASTERN 19-DEC-07 07.29.34.862000 PM -06:00
(MY os in -6:00 time zone :))#2; Sat, 23 Feb 2008 19:26:00 GMT
- For a good explanation of why read Metalink note 340512.1.
In short, it's because systimestamp calls an OS function to get the values, which normally only contains the modified time (ie GMT +/- the TZ offset). Since there are many timezone regions which map back to a given offset, it's virtually impossible to work out from the offset which timezone you would want returned. Since you have set the DB timezone, try this:
SELECT systimestamp AT TIME ZONE dbtimezone FROM DUAL;
Chris#3; Sat, 23 Feb 2008 19:27:00 GMT
- This Chris. That's been a great help.#4; Sat, 23 Feb 2008 19:28:00 GMT