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 sections show statement results using the default time zone, America/Los_Angeles, and the statement results using a user-specified time zone of Etc/UTC.

America/Los_Angeles default time zone

The following examples show various ways to change the timestamp. The Spanner database uses the default time zone, America/Los_Angeles, which has an offset of UTC-8 for the specified dates.

Statement: 

SELECT TIMESTAMP("2072-12-25 15:30:00") AS tim estamp_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 |
 ----------------------/

Etc/UTC time zone

The following examples show various ways to change the timestamp. The Spanner database uses the user-defined time zone, Etc/UTC, which has an offset of UTC+0 for the specified dates.

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 |
 ----------------------/