timezone - Amazon Redshift

timezone

Values (default in bold)

UTC, time zone

Syntax

SET timezone { TO | = } [ time_zone | DEFAULT ] SET time zone [ time_zone | DEFAULT ]

Description

Sets the time zone for the current session. The time zone can be the offset from Universal Coordinated Time (UTC) or a time zone name.

Note

You can't set the timezone configuration parameter by using a cluster parameter group. The time zone can be set only for the current session by using a SET command. To set the time zone for all sessions run by a specific database user, use the ALTER USER command. ALTER USER … SET TIMEZONE changes the time zone for subsequent sessions, not for the current session.

When you set the time zone using the SET timezone (one word) command with either TO or =, you can specify time_zone as a time zone name, a POSIX-style format offset, or an ISO-8601 format offset, as shown following.

SET timezone { TO | = } time_zone

When you set the time zone using the SET time zone command without TO or =, you can specify time_zone using an INTERVAL and also a time zone name, a POSIX-style format offset, or an ISO-8601 format offset, as shown following.

SET time zone time_zone

Time zone formats

Amazon Redshift supports the following time zone formats:

  • Time zone name

  • INTERVAL

  • POSIX-style time zone specification

  • ISO-8601 offset

Because time zone abbreviations, such as PST or PDT, are defined as a fixed offset from UTC and don't include daylight savings time rules, the SET command doesn't support time zone abbreviations.

For more details on time zone formats, see the following.

Time zone name – The full time zone name, such as America/New_York. Full time zone names can include daylight savings rules.

The following are examples of time zone names:

  • Etc/Greenwich

  • America/New_York

  • CST6CDT

  • GB

Note

Many time zone names, such as EST, MST, NZ, and UCT, are also abbreviations.

To view a list of valid time zone names, run the following command.

select pg_timezone_names();

INTERVAL – An offset from UTC. For example, PST is –8:00 or –8 hours.

The following are examples of INTERVAL time zone offsets:

  • –8:00

  • –8 hours

  • 30 minutes

POSIX-style format – A time zone specification in the form STDoffset or STDoffsetDST, where STD is a time zone abbreviation, offset is the numeric offset in hours west from UTC, and DST is an optional daylight-savings zone abbreviation. Daylight savings time is assumed to be one hour ahead of the given offset.

POSIX-style time zone formats use positive offsets west of Greenwich, in contrast to the ISO-8601 convention, which uses positive offsets east of Greenwich.

The following are examples of POSIX-style time zones:

  • PST8

  • PST8PDT

  • EST5

  • EST5EDT

Note

Amazon Redshift doesn't validate POSIX-style time zone specifications, so it is possible to set the time zone to an invalid value. For example, the following command doesn't return an error, even though it sets the time zone to an invalid value.

set timezone to ‘xxx36’;

ISO-8601 Offset – The offset from UTC in the form ±[hh]:[mm].

The following are examples of ISO-8601 offsets:

  • -8:00

  • +7:30

Examples

The following example sets the time zone for the current session to New York.

set timezone = 'America/New_York';

The following example sets the time zone for the current session to UTC–8 (PST).

set timezone to '-8:00';

The following example uses INTERVAL to set the time zone to PST.

set timezone interval '-8 hours'

The following example resets the time zone for the current session to the system default time zone (UTC).

set timezone to default;

To set the time zone for database user, use an ALTER USER … SET statement. The following example sets the time zone for dbuser to New York. The new value persists for the user for all subsequent sessions.

ALTER USER dbuser SET timezone to 'America/New_York';