Reference

• Functions

  • date and time

    • date

    • time

      • timestamp

    • datetime

Timestamp vs Datetime

timestamps are used for absolut points in time in a timezone, e.g. a flight departure, and they are recorded in the UTC timezone

datetimes are used for events independed from the UTC time zones, e.g. a birthday

many functions that exist for the timestamp exist for the datetime too

formatted timestamps with FORMAT_TIMESTAMP() and datetimes with FORMAT_DATETIME() are always a string data type, but you can use SAFECAST

Time Zone

The  time zone is UTC, if not specified otherwise.

TIMESTAMP()

TIMESTAMP() without a zone creates automatically UTC

create 2022-07-01 00:00:00 UTC with:

TIMESTAMP('2022-07-01 00:00:00')

TIMESTAMP('2022-07-01 00:00:00','UTC')

you can assign a new time zone to a date, e.g 'Europe/Berlin'

create 2022-06-30 22:00:00 UTC for the time 2022-07-01 00:00:00 in Europe/Berlin:

TIMESTAMP('2022-07-01 00:00:00','Europe/Berlin')

DATETIME()

create quite similar 2022-06-30T22:00:00 as datetime for 2022-07-01 00:00:00 in Europe/Berlin:

DATETIME('2022-07-01 00:00:00','Europe/Berlin')

UTC+n Notation

UTC+n can indicate the time zone. The output is not the UTC time, but the time that is in UTC+n hours. Both time values are the same here:

SELECT

  FORMAT_DATETIME('%F %T', CURRENT_DATETIME('Europe/Berlin')) AS tzde,

  FORMAT_TIMESTAMP('%F %T %Z', CURRENT_TIMESTAMP(),'Europe/Berlin') AS tzde_utc_zone_notation;

Unix Timestamp

create an utc timestamp from an unix timestamp, e.g.:

TIMESTAMP_SECONDS(SAFE_CAST('1713330159' AS INT64)) AS unix_to_utc

you might need a numeric first:

TIMESTAMP_SECONDS(SAFE_CAST(SAFE_CAST(dataload_id AS NUMERIC) AS INT64)) AS unix_to_utc

create an unix timestamp from an utc timestamp, e.g.:

UNIX_SECONDS(CURRENT_TIMESTAMP()) AS utc_to_unix