TIMESTAMP
The "Timestamp" datatype has no concept of timezones, it's basically the same as the "date" dataype but with added precision.
select * from scott.emp
where to_timestamp(hiredate) = TIMESTAMP'2003-04-07 00:00:00 CET';
TIMEZONE
Oracle has 2 datatypes who can store timezones: TimeStamp with Time Zone (TSTZ) and TimeStamp with Local Time Zone (TSLTZ).
TimeStamp with Time Zone (TSTZ) data stores the time and the actual timezone offset/name used for that time at the moment of insert. The stored timezone information can be an offset or named timezone.
The date format for
TIMESTAMP WITH TIME ZONE is determined by the value of the
NLS_TIMESTAMP_TZ_FORMAT parameter.
TimeStamp with Local Time Zone (TSLTZ) data stores internally the time converted to/from the database timezone (see point 3) from the timezone specified at insert/select time.
Note that the data stored in the database is normalized to the database time zone, and the time zone offset is not stored as part of the column data, the current
DBTIMZONE is used. When users retrieve the data, Oracle Database returns it in the users' local session time zone from the current
DBTIMEZONE.
The date format for
TIMESTAMP WITH LOCAL TIME ZONE is determined by the value of the
NLS_TIMESTAMP_FORMAT parameter.
TIMESTAMP WITH LOCAL TIME ZONE data is NOT returned with a timezone in the result set.
If you store TSLTZ data the database timezone should always be an offset.
Note that the
SESSIONTIMEZONE (and NOT the database timezone) is the actual used timezone at both insert and select (if not specified explicit) to calculate the time for the inserted/returned result.
What is the difference between a timezone offset and a named timezone?
A timezone expressed in a offset (example: -04:00) is always the same and gives the difference compared to UTC (Coordinated Universal Time), practically (although not correct technically speaking) UTC is the same as GMT (Greenwich mean time), and UTC/GMT corresponds to a offset of +00:00.
Because a offset is fixed it cannot change reflect DST (Daylight Saving Time) changes. DST itself is a change of the offset.
A named timezone is a name (example: Canada/Eastern or GMT) who reflects a geographic region or location and this will be mapped to a certain offset for a certain time on a certain date. The offset may or may not change for DST or have been changed during history.
The named timezone Europe/London for example will have a different offset during summer (+01:00) as during winter (+00:00) and Oracle has the definitions on when this DST change happens stored, so it can adjust it at the right date and time.
The named timezone UTC for example is always +00:00.
Database Timezone (dbtimezone)
Below excerpts from
MOS Article 340512.1:
The database time zone is not as important as it sounds. First of all it does not influence functions like
SYSDATE, or
SYSTIMESTAMP. These function take their contents (date and time, and in the case of
SYSTIMESTAMP also time zone) completely from the OS without any "Oracle" intervention.
The only function of the database time zone is that it functions as a time zone in which the values of the "
TIMESTAMP WITH LOCAL TIME ZONE" (TSLTZ) datatype are normalized to the current database timezone when they are stored in the database. However, these stored values are always converted into the session time zone on insert and retrieval, so the actual setting of the database time zone is more or less immaterial. The dbtimezone should be set to an offset (+00:00 , -05:00 or +09:00 for example) or a static time zone that is not affected by DST (like UTC or GMT ).
A common misconception is that the database timezone needs to be "your" timezone. This is NOT true. The database timezone has NO relation with "where" the server is located.
There is NO advantage whatsoever in using your timezone or a named timezone as database timezone.
The best setting for dbtimezone is simply +00:00 (or any other OFFSET like -09:00, +08:00, ...), if your current dbtimezone value is an OFFSET then please leave it like it is. The database time zone is only used as a time zone in which stored TSLTZ values are normalized.
So the value of the dbtimezone should in fact not change. Because this is the only task for the database time zone it should not be used for any other things.
The database time zone is usually only set at creation time of the database:
SQL> CREATE DATABASE...
SET TIME_ZONE='+00:00';
This will only work if there are no TSLTZ values already stored in the database or an ORA-02231 (9i) or ORA-30079 will be seen
SYSTIMESTAMP
You could say that
SYSTIMESTAMP is "
SYSDATE with time zone information added".
SYSTIMESTAMP, just like
SYSDATE depends on Unix platforms on the UNIX time configuration (= Unix TZ variable) for the Unix session when the database and listener where started.
The precision is platform dependant, on most Unix platforms it's microseconds (10-6) on Windows this is Milliseconds (10-3). The output is defined by
NLS_TIMESTAMP_TZ_FORMAT in
NLS_SESSION_PARAMETERS.
The
SYSTIMESTAMP output has an offset from UTC but is not defined to include an actual named timezone. Mapping this offset to a timezone, or reinterpreting the OS TZ setting would be very hard and error prone and so the code sticks to the absolute offset between UTC and the local time.
You can see this by issuing:
SQL> SELECT EXTRACT( timezone_region from systimestamp ) FROM dual;
EXTRACT(TIMEZONE_REGIONFROMSYSTIMESTAMP)
----------------------------------------------------------------
UNKNOWN
if you want to know the time in a particular time zone you can use "AT TIME ZONE".
SQL> SELECT systimestamp AT TIME ZONE 'Canada/Eastern' FROM DUAL;
SYSTIMESTAMPATTIMEZONE'CANADA/EASTERN'
------------------------------------------------------------------
19-NOV-07 09.47.55.099000 CANADA/EASTERN
if you are wanting to see the timestamp in the sessions timezone then you should use
CURRENT_TIMESTAMP.
SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='YYYY.MM.DD HH24:MI:SS TZR TZD';
Session altered.
SQL> ALTER SESSION SET TIME_ZONE = 'Canada/Eastern';
Session altered.
SQL> SELECT current_timestamp FROM dual;
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
2007.11.19 09:48:18 CANADA/EASTERN EST
Please note that the DBTIMEZONE has NOTHING to do with this, changing DBTIMEZONE will NOT solve SYSDATE or SYSTIMESTAMP returning a wrong time.
Difference between CURRENT_DATE, LOCALTIMESTAMP and CURRENT_TIMESTAMP?
They all depend on the session timezone, which is defined on the CLIENT side, not server side.
CURRENT_DATE returns the current date and time in the session time zone in a value of datatype DATE.
LOCALTIMESTAMP returns the current date and time in the session time zone in a value of datatype TIMESTAMP.
CURRENT_TIMESTAMP returns the current date and time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE.
The sessions NLS_DATE_FORMAT defines the output format of a DATE, NLS_TIMESTAMP_FORMAT defines the output format of a TIMESTAMP, the NLS_TIMESTAMP_TZ_FORMAT defines the output format of a TIMESTAMP WITH TIME ZONE.
How can I check the session time zone?
The SESSIONTIMEZONE sql function returns the value of the current session's time zone:
SQL> SELECT SESSIONTIMEZONE FROM DUAL;
SESSIONTIMEZONE
---------------
+01:00
How can I set the session time zone?
By default the session timezone is set to the OFFSET of the clients
(!) operating system timezone value/setting at connection time. The
client asks the client Os what the current offset is to UTC and then ,
during the connection/session creation fase , does an alter session
based on this.
The session time zone can be explicitly set to:
- O/S local time zone
- Database time zone
- An absolute offset
- A named region
This can be done in 2 ways, the first method consists to use one of the following ALTER SESSION SET TIME_ZONE statements:
SQL> ALTER SESSION SET TIME_ZONE = local;
SQL> ALTER SESSION SET TIME_ZONE = dbtimezone;
SQL> ALTER SESSION SET TIME_ZONE = '-05:00';
SQL> ALTER SESSION SET TIME_ZONE = 'Europe/London';
The alternative method is to set the (client) operating system environment variable ORA_SDTZ:
ORA_SDTZ = 'OS_TZ' | 'DB_TZ' | '[+ | -] hh:mm' | 'timezone_region'
$ ORA_SDTZ='OS_TZ'
$ export ORA_SDTZ
$ ORA_SDTZ='-05:00'
$ export ORA_SDTZ
If you do not want to set ORA_SDTZ on all client machines, but still
want to be in control over the session time zone settings for all
sessions, then consider using a logon trigger to the database, in which
you can set the session time zone specifically through ALTER SESSION (as
per point a above).
Note that the session timezone defaults to an offset
( like +05:00), even if the unix TZ variable or Windows timezone region
is set to a named TZ. If you need the session timezone to be a named
timezone then you need to set ORA_SDTZ client (!) environment (or
registry on windows) with an Oracle TZ name.
How can I retrieve the time zone offset corresponding to a time zone region?
The TZ_OFFSET() sql function returns the time zone offset displacement to the input time zone region.
SQL> SELECT TZ_OFFSET('US/Pacific') FROM DUAL;
TZ_OFFS
-------
-07:00
The returned offset depends on the date this statement is executed. For example, in the 'US/Pacific' time zone, it may return '-07:00' or '-08:00' whether daylight saving is in effect or not.
Other Resources
To see a listing of valid time zone region names, query the TZNAME column of the V$TIMEZONE_NAMES dynamic performance view.
SELECT SESSIONTIMEZONE FROM DUAL;
http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions162.htm#SQLRF51736
ALTER SESSION SET
TIME_ZONE = '[+ | -] hh:mm'
| LOCAL
| DBTIMEZONE
| 'time_zone_region'
MOS:
- Briefing by Jonathan Lewis, particularly interesting when considering a column type TIMESTAMP WITH LOCAL TIME ZONE
- 340512.1 Timestamps & time zones – Frequently Asked Questions
- ALTER SESSION Reference.
