Set the default time zone of a database

Spanner provides date and timestamp functions in GoogleSQL and Cloud SQL for PostgreSQL. Some functions, such as TIMESTAMP, are time zone dependent and accept an optional time zone parameter. If no time zone parameter is provided in a function, Spanner databases default to the America/Los_Angeles time zone.

Spanner lets you change the default time zone of a database to customize 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's default time zone for that statement.
  • All timestamps in the REST and RPC APIs must use UTC and end with an uppercase Z.
  • Timestamps in query results are consistently presented in UTC, with Z appended. 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 the Cloud Spanner Database Admin (roles/spanner.databaseAdmin) IAM role on the database. For more information about granting roles, see Manage 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 the Required 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 with custom roles or other predefined roles.

Set the default time zone

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

GoogleSQL

Use the ALTER DATABASE statement:

ALTER DATABASE DATABASE-NAME SET OPTIONS (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 database default to. Must be a valid entry from the IANA Time Zone Database. For example, Etc/UTC.

PostgreSQL

Use the ALTER DATABASE statement:

ALTER DATABASE DATABASE-NAME SET spanner.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 database default to. Must be a valid entry from the IANA 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 Spanner uses America/Los_Angeles as the default time zone. America/Los_Angeles has an offset of UTC-8 for timestamps in the following examples.

Statement:

SELECT TIMESTAMP("2072-12-25 15:30:00") AS timestamp_str;

Output:

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

Statement:

SELECT EXTRACT(HOUR FROM TIMESTAMP("2072-12-25 15:30:00")) AS hour;

Output:

/*------*
 | hour |
 +------+
 | 23   |
 *------*/

Statement:

SELECT TIMESTAMP_TRUNC(TIMESTAMP "2072-12-25 15:30:00", DAY) AS date_str;

Output:

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

Default time zone option set to Etc/UTC

The following examples show how the same statements behave when the default time zone option is set to Etc/UTC.

Statement:

SELECT TIMESTAMP("2072-12-25 15:30:00") AS timestamp_str;

Output:

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

Statement:

SELECT EXTRACT(HOUR FROM TIMESTAMP("2072-12-25 15:30:00")) AS hour;

Output:

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

Statement:

SELECT TIMESTAMP_TRUNC(TIMESTAMP "2072-12-25 15:30:00+00", DAY) AS date_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, the database's default time zone isn't applied.

Statement:

SELECT FORMAT_TIMESTAMP("%c", TIMESTAMP "2050-12-25 15:30:55+00", "Australia/Sydney")
  AS formatted;

Output:

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

Statement:

SELECT TIMESTAMP("2072-12-25 15:30:00+11:00") AS timestamp_str;

Output:

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