Set the default time zone of a database

Spanner providesdateandtimestampfunctions in GoogleSQLand Cloud SQL for PostgreSQL. Some functions, such asTIMESTAMP,are time zone dependent and accept an optional time zone parameter. If no timezone parameter is provided in a function, Spanner databasesdefault to theAmerica/Los_Angeles time zone.

Spanner lets you change the default time zone of a database tocustomize this behavior.

Limitations

  • You can only change the time zone of empty databases without any tables.
  • Providing a time zone parameter within a statement overrides the database'sdefault time zone for that statement.
  • All timestamps in theREST and RPC APIsmust use UTC and end with an uppercaseZ.
  • Timestamps in query results are consistently presented in UTC, withZappended. Display time zone conversions are not performed.

Required roles

To get the permissions that you need to set the default time zone of a database, ask your administrator to grant you theCloud Spanner Database Admin (roles/spanner.databaseAdmin) IAM role on the database. For more information about granting roles, seeManage access to projects, folders, and organizations.

This predefined role contains the permissions required to set the default time zone of a database. To see the exact permissions that are required, expand theRequired permissions section:

Required permissions

The following permissions are required to set the default time zone of a database:

  • set the default time zone of a database: spanner.databases.getDdl, spanner.databases.updateDdl

You might also be able to get these permissions withcustom roles or otherpredefined roles.

Set the default time zone

To change the default time zone of your database, run the following statement:

GoogleSQL

Use theALTER DATABASE statement:

ALTERDATABASEDATABASE-NAMESETOPTIONS(default_time_zone='TIME-ZONE-NAME');

Replace the following:

  • DATABASE-NAME: the name of the database. For example,my-database.
  • TIME-ZONE-NAME: the name of the time zone to set the databasedefault to. Must be a valid entry from theIANA Time Zone Database. For example,Etc/UTC.

PostgreSQL

Use theALTER DATABASE statement:

ALTERDATABASEDATABASE-NAMESETspanner.default_time_zone='TIME-ZONE-NAME';

Replace the following:

  • DATABASE-NAME: the name of the database. For example,my-database.
  • TIME-ZONE-NAME: the name of the time zone to set the databasedefault to. Must be a valid entry from theIANA Time Zone Database. For example,Etc/UTC.

Examples

The following example queries show how to use the default time zone option.

Default time zone not customized

If the default_time_zone option is not explicitly set in the database schema,then the value of default_time_zone is null and SpannerusesAmerica/Los_Angeles as the default time zone.America/Los_Angeles hasan offset of UTC-8 for timestamps in the following examples.

Statement:

GoogleSQL

SELECTTIMESTAMP("2072-12-25 15:30:00")AStimestamp_str;

PostgreSQL

SELECT'2072-12-25 15:30:00'::timestamptzAStimestamp_str;

Output:

/*----------------------* | timestamp_str        | +----------------------+ | 2072-12-25T23:30:00Z | *----------------------*/

Statement:

GoogleSQL

SELECTEXTRACT(HOURFROMTIMESTAMP("2072-12-25 15:30:00Z"))AShour;

PostgreSQL

SELECTEXTRACT(HOURFROM'2072-12-25 15:30:00Z'::timestamptz)AShour;

Output:

/*------* | hour | +------+ |  7   | *------*/

Statement:

GoogleSQL

SELECTTIMESTAMP_TRUNC(TIMESTAMP"2072-12-25 15:30:00Z",DAY)ASdate_str;

PostgreSQL

SELECTDATE_TRUNC('day',TIMESTAMPTZ'2072-12-25 15:30:00Z')ASdate_str;

Output:

/*----------------------* | date_str             | +----------------------+ | 2072-12-25T08:00:00Z | *----------------------*/

Default time zone option set toEtc/UTC

The following examples show how the same statements behave when thedefault time zone option is set toEtc/UTC.

Statement:

GoogleSQL

SELECTTIMESTAMP("2072-12-25 15:30:00")AStimestamp_str;

PostgreSQL

SELECT'2072-12-25 15:30:00'::timestamptzAStimestamp_str;

Output:

/*----------------------* | timestamp_str        | +----------------------+ | 2072-12-25T15:30:00Z | *----------------------*/

Statement:

GoogleSQL

SELECTEXTRACT(HOURFROMTIMESTAMP("2072-12-25 15:30:00Z"))AShour;

PostgreSQL

SELECTEXTRACT(HOURFROM'2072-12-25 15:30:00Z'::timestamptz)AShour;

Output:

/*------* | hour | +------+ | 15   | *------*/

Statement:

GoogleSQL

SELECTTIMESTAMP_TRUNC(TIMESTAMP"2072-12-25 15:30:00Z",DAY)ASdate_str;

PostgreSQL

SELECTDATE_TRUNC('day',TIMESTAMPTZ'2072-12-25 15:30:00Z')ASdate_str;

Output:

/*----------------------* | date_str             | +----------------------+ | 2072-12-25T00:00:00Z | *----------------------*/

Default time zone overridden by function parameter

When a function or string literal includes a defined time zone parameter, thedatabase's default time zone isn't applied.

GoogleSQL

Statement:

SELECTFORMAT_TIMESTAMP("%c",TIMESTAMP"2050-12-25 15:30:55+00","Australia/Sydney")ASformatted;

Output:

/*--------------------------*| formatted                |+--------------------------+| Mon Dec 26 02:30:55 2050 |*--------------------------*/

Statement:

SELECTTIMESTAMP("2072-12-25 15:30:00+11:00")AStimestamp_str;

Output:

/*----------------------*| timestamp_str        |+----------------------+| 2072-12-25T04:30:00Z |*----------------------*/

PostgreSQL

Statement:

SELECT'2072-12-25 15:30:00+11:00'::timestamptzAStimestamp_str;

Output:

/*----------------------*| timestamp_str        |+----------------------+| 2072-12-25T04:30:00Z |*----------------------*/

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-15 UTC.