Oracle PL/SQL Tutorial/SQL Data Types/Timezone

Change timezone

   <source lang="sql">

SQL> ALTER SESSION SET TIME_ZONE = "EST" SQL> SELECT CURRENT_TIMESTAMP from dual; CURRENT_TIMESTAMP

---

31-MAY-07 08.35.20.406000 PM -07:00 SQL></source>

The Database Time Zone and Session Time Zone

The time zone for the database is the database time zone.

The time zone set for your database session is the session time zone.

The database time zone is set by the TIME_ZONE database parameter.

The setting of the TIME_ZONE parameter is in init.ora or spfile.ora file.

By default, the session time zone is the same as the database time zone.

You can change the session time zone using the ALTER SESSION statement to set the TIME_ZONE parameter.

Setting the session time zone doesn"t change the database time zone.

For example, the following statement sets the local time zone to Pacific Standard Time (PST):

   <source lang="sql">

ALTER SESSION SET TIME_ZONE = "PST";</source>

Time Zone-Related Functions

Function Description CURRENT_DATE() Returns the current date in the local time zone set for the database session. DBTIMEZONE() Returns the time zone for the database. NEW_TIME(x, time_zone1, time_zone2) Converts x from time_zone1 to time_zone2 and returns the new datetime. SESSIONTIMEZONE() Returns the time zone for the database session. TZ_OFFSET(time_zone) Returns the offset for time_zone in hours and minutes.

Understanding Time Zones

A time zone is an offset from the time in Greenwich, England.

The time in Greenwich was once known as Greenwich Mean Time (GMT).

It is now known as Coordinated Universal Time.

You specify a time zone using either an offset from UTC or the name of the region.

When you specify an offset, you use HH:MI prefixed with a plus or minus sign:

+|-HH:MI

where

1. + or - indicates an increase or decrease for the offset from UTC.
2. HH:MI indicates the time zone hour and minute for the offset.

You may also specify a time zone using the name of a region.

For example, PST indicates Pacific Standard Time, which is seven hours behind UTC.

EST indicates Eastern Standard Time, which is four hours behind UTC.

Quote from:

Oracle Database 10g SQL (Osborne ORACLE Press Series) (Paperback)

# Paperback: 608 pages

# Publisher: McGraw-Hill Osborne Media; 1st edition (February 20, 2004)

# Language: English

# ISBN-10: 0072229810

# ISBN-13: 978-0072229813

10. 13. Timezone 10. 13. 1. Understanding Time Zones 10. 13. 2. <A href="/Tutorial/Oracle/0200__SQL-Data-Types/TimeZoneRelatedFunctions.htm">Time Zone-Related Functions</a> 10. 13. 3. <A href="/Tutorial/Oracle/0200__SQL-Data-Types/TheDatabaseTimeZoneandSessionTimeZone.htm">The Database Time Zone and Session Time Zone</a> 10. 13. 4. <A href="/Tutorial/Oracle/0200__SQL-Data-Types/Changetimezone.htm">Change timezone</a>