CREATE TIME ZONE statement

Creates a time zone object that can be used by the database to simulate a time zone that is different from the server's time zone.

Syntax

CREATE [ OR REPLACE ] TIME ZONE 
name time-zone-option [ ... ]

time-zone-option : 
 { OFFSET offset
 | DST OFFSET offset
 | NO DST
 | [ DST ] STARTING month-day-rule AT { minutes | hours:minutes }
 | [ DST ] ENDING month-day-rule AT { minutes | hours:minutes } }

offset :
'{ + | -} { minutes | hours:minutes } '

month-day-rule :
'month / day-rule'

day-rule : 
number
| LAST day
| day >= number

Parameters

OR REPLACE clause
Creates a new time zone or replaces an existing time zone with the same name. If the time zone already exists and OR REPLACE is not specified, then the statement fails.
offset clause
Specifies the standard offset for the time zone in hours and minutes ahead of UTC. If the offset is a whole number, then it indicates minutes, so 1:00 and 60 are equivalent. The offset cannot be more than +23:59 (1439 minutes) or less than -23:59 (-1439 minutes).
DST OFFSET clause
Specifies the difference from standard time that results from using daylight savings time. The default is NO DST if none of the DST clauses are used. If the STARTING and ENDING clauses are specified but the DST OFFSET clause is not, then the default is one hour.
NO DST clause
Specifies that the time zone does not observe daylight savings time. You cannot use the STARTING, ENDING, or DST OFFSET clauses with this clause.
STARTING clause
Specifies the date and time when daylight savings time begins.
ENDING clause
Specifies the date and time when daylight savings time ends.
- month-day-rule
  month is a number from 1 to 12 or an English three-letter short form (Jan, Feb, and so on).
- day-rule
  The day of the month on which to start or end daylight savings time.
  - number
    A number from 1 to 31. If specifying number, then day-rule is a date within the month. For example, specifying Mar/12 for month-day-rule indicates March 12th.
  - last day
    The last specified week day of the month. For example, specifying last Fri, indicates the last Friday of the month.
  - day
    A number from 1 to 7 or an English three-letter short form (Mon, Tue, and so on).
- day >= number
  The earliest day either on or after a particular date of the month. number must be less than or equal to the number of days in the month. For example, Feb/Mon>=28.
  
  Note day >= number may match a date in a different month. For example, the DST rule 'Sep / Wed >= 25' (the first Wednesday after September 25), might be in October, depending on the year.

Remarks

The DST clauses are optional. If the time zone observes daylight savings time, then the STARTING and ENDING clauses must be specified.

Multiple simulated time zone objects can be created for a database. If you have started two or more databases, each database can run in its own simulated time zone that is different from the server's actual time zone. Use the SET OPTION PUBLIC.time_zone statement to switch to the desired time zone.

Privileges

You must have the MANAGE TIME ZONE system privilege to create time zones.

Side effects

Automatic commit.

Executing this statement populates the ISYSTIMEZONE system table.

Example

To add the Eastern Time zone, execute the following statement:

CREATE TIME ZONE EST5EDT OFFSET '-05:00'
STARTING 'Mar/Sun>=8' AT '2:00'
ENDING 'Nov/Sun>=1' AT '2:00';

The time zone is five hours behind UTC. Daylight savings time starts at 2:00 A.M. on the first Sunday on or after March 8 (the second Sunday of March), and ends at 2:00 A.M. on the first Sunday of November.

To add the Australian Eastern Time zone, execute the following statement:

CREATE TIME ZONE NewSouthWales OFFSET '10:00'
STARTING 'Oct/Sun>=1' AT '2:00'
ENDING 'Apr/Sun>=1' AT '2:00';

The time zone is ten hours ahead of UTC. Daylight savings time begins on the first Sunday of October at 2:00 A.M., and ends on the first Sunday of April at 2:00 A.M.

To switch to the NewSouthWales time zone, execute the following statement:

SET OPTION PUBLIC.time_zone='NewSouthWales';

To return to the server's time zone, execute the following statement:

SET OPTION PUBLIC.time_zone=;