Text description of dbtimezone Purpose The DBTIMEZONE function returns the value of the database time zone. The return type is a time zone offset (a character type in the format '[+|-]TZH:TZM') or a time zone region name, depending on how the user specified the database time zone value in the most recent CREATE DATABASE or ALTER DATABASE statement. Example The following example assumes that the database time zone is set to UTC time zone: SELECT DBTIMEZONE FROM DUAL;DBTIME ------ -08:00
Concerning timezones and oracle, Note that in 9i there is a datatype "timestamp with timezone". The timestamp can store the date, time (down to 9 decimal places with seconds), and the TIMEZONE. The database "understands" timezones so if you get the difference between 1pm PST and 4pm EST it understands that it is in fact the same time. And so on. In short, 9i supports and is timezone aware. 8i and before -- not so. You will have to write a piece of Java code to help you. (let me know it you want me to post an example) It can be queried and altered as follows: ============================================ [email protected]> select dbtimezone from dual; DBTIME ------ -04:00 [email protected]> alter database set time_zone = 'EST'; Database altered. [email protected]> select dbtimezone from dual; DBTIME ------ -04:00 [email protected]> connect / as sysdba; Connected. [email protected]> shutdown Database closed. Database dismounted. ORACLE instance shut down. [email protected]> startup ORACLE instance started. Total System Global Area 358909124 bytes Fixed Size 279748 bytes Variable Size 335544320 bytes Database Buffers 16777216 bytes Redo Buffers 6307840 bytes Database mounted. Database opened. [email protected]> select dbtimezone from dual; DBTI ---- EST ============================================ Also, if you need to remember both the clients time AND the server time -- It is wiser to store 2 dates. That will be the best way to avoid all confusion. In that way you can query either column to get the correct representation of the date/time given the circumstances AND you'll be able to use indexes on the individual columns to speed up queries on dates if need be. Oracle has a calendar/resource system built internally. Oracle has opted to store all dates in GMT in the database (it's used worldwide) and when needed, store the datetime in the timezone of the client as well. Oracle predominatly use the GMT date and convert it to the local timezone on the fly but on the occasions when needed -- it keeps the clients native date in a column as well. Normally application development in environments where we have entries made and that are time zone sensitive, we use use an application context -- this will have an OFFSET that we can add to a DATE to convert from the server TZ to any TZ we want. Hope this helps! Best wishes for New year 2003
somebody have better idea?????????
dbtimezone get offset of UTC. but I want GMT time.UTC=GMT???
NEW_TIME(SYSDATE,TZ_FROM,'GMT')
但是，如何取得当前数据库的时区是'EST'或是其他呢？？？？
NEW_TIME(SYSDATE,TZ_FROM,'GMT')
但是，如何取得当前数据库的时区是'EST'或是其他呢？？？？
如何把系统时间转换为GMT时间呢？？？？
NEW_TIME(SYSDATE,TZ_FROM,'GMT')
但是，如何取得当前数据库的时区是'EST'或是其他呢？？？？
如何把系统时间转换为GMT时间呢？？？？
NEW_TIME(SYSDATE,TZ_FROM,'GMT')
但是，如何取得当前数据库的时区是'EST'或是其他呢？？？？
如何把系统时间转换为GMT时间呢？？？？
Syntax
dbtimezone::=
Text description of dbtimezone
Purpose
The DBTIMEZONE function returns the value of the database time zone. The return type is a time zone offset (a character type in the format '[+|-]TZH:TZM') or a time zone region name, depending on how the user specified the database time zone value in the most recent CREATE DATABASE or ALTER DATABASE statement. Example
The following example assumes that the database time zone is set to UTC time zone: SELECT DBTIMEZONE FROM DUAL;DBTIME
------
-08:00
It can be queried and altered as follows:
============================================
[email protected]> select dbtimezone from dual; DBTIME
------
-04:00 [email protected]> alter database set time_zone = 'EST'; Database altered. [email protected]> select dbtimezone from dual; DBTIME
------
-04:00 [email protected]> connect / as sysdba;
Connected.
[email protected]> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
[email protected]> startup
ORACLE instance started. Total System Global Area 358909124 bytes
Fixed Size 279748 bytes
Variable Size 335544320 bytes
Database Buffers 16777216 bytes
Redo Buffers 6307840 bytes
Database mounted.
Database opened.
[email protected]> select dbtimezone from dual; DBTI
----
EST ============================================
Also, if you need to remember both the clients time AND the server time -- It is wiser to store 2 dates. That will be the best way to avoid all confusion. In that way you can query either column to get the correct representation of the date/time given the circumstances AND you'll be able to use indexes on the individual columns to speed up queries on dates if need be. Oracle has a calendar/resource system built internally. Oracle has opted to store all dates in GMT in the database (it's used worldwide) and when needed, store the datetime in the timezone of the client as well. Oracle predominatly use the GMT date and convert it to the local timezone on the fly but on the occasions when needed -- it keeps the clients native date in a column as well. Normally application development in environments where we have entries made and that are time zone sensitive, we use use an application context -- this will have an OFFSET that we can add to a DATE to convert from the server TZ to any TZ we want.
Hope this helps! Best wishes for New year 2003
but I want GMT time.UTC=GMT???
gz
NEW_TIME(SYSDATE,TZ_FROM,'GMT')
但是，如何取得当前数据库的时区是'EST'或是其他呢？？？？
NEW_TIME(SYSDATE,TZ_FROM,'GMT')
但是，如何取得当前数据库的时区是'EST'或是其他呢？？？？