Reference

• Functions

  • date and time

    • date

    • time

      • timestamp

        • format

          • format elements

        • truncate

    • datetime

Format

FORMAT_TIMESTAMP()

FORMAT_TIMESTAMP() will create always a string data type

use this for timestamps

create the current timestamp for Europe/Berlin with:

FORMAT_TIMESTAMP('%F %T', CURRENT_TIMESTAMP(), 'Europe/Berlin')

create 2022-07-01 02:00:00 UTC+2 from the UTC time 2022-07-01 00:00:00 with:

FORMAT_TIMESTAMP('%F %T %Z', '2022-07-01 00:00:00', 'Europe/Berlin')

create 2022-07-01 02:00:00 UTC+2 from the UTC time 2022-07-01 00:00:00 with:

FORMAT_TIMESTAMP('%F %T %Z', TIMESTAMP('2022-07-01 00:00:00'), 'Europe/Berlin')

Format Elements

see all format elements

• '%F' ceates YYYY-MM-DD

• '%T' creates hh:mm:dd

• '%F %T' creates YYYY-MM-DD hh:mm:dd

• '%Y-%m-%d %H:%M:%S' creates YYYY-MM-DD hh:mm:dd

• '%Y-01-01 00:00:00' creates the start of the year

• '%Y-12-31 23:00:00' creates the start of the year

• '%H:59:59' creates last second of the hour

• '%V' creates ISO week number

• '%G' creates year to ISO week number

• '%G-W%V' creates an ISO week number date

• '%u' creates the weekday number for Mon 1 to Sun 7,

• '%s' creates count of seconds since 1970-01-01 00:00:00 viz. Unix time.

• '%Z' indicates the UTC time zone off-set

Weeknumber

ISO weeknumber, e.g.

create 2020-W53 with:

FORMAT_TIMESTAMP('%G-W%V','2021-01-01')

create 2020-W01 with: 

CONCAT(EXTRACT(ISOYEAR FROM DATE '2019-12-31'),'W-',EXTRACT(ISOWEEK FROM DATE '2019-12-31'))

Truncate Timestamp

get rid of milliseconds with:

TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(),second)

create 2021-09-25 13:00:00 UTC with:

TIMESTAMP_TRUNC(TIMESTAMP('2021-09-25 13:45:10'),hour)

create 2021-09-25 00:00:00 UTC with:

TIMESTAMP_TRUNC(TIMESTAMP('2021-09-25 13:45:10'),day)

  create 2021-09-01 00:00:00 UTC with:

TIMESTAMP_TRUNC(TIMESTAMP('2021-09-25 13:45:10'),month)

   create 2021-01-01 00:00:00 UTC with:

TIMESTAMP_TRUNC(TIMESTAMP('2021-09-25 13:45:10'),year)

Mind the time zone! Be aware, a change of the time zone can have an impact on the date and time!

create the start of the current day in UTC for Europe/Berlin with:

TIMESTAMP(DATETIME_TRUNC(CURRENT_DATETIME('Europe/Berlin'),day),'Europe/Berlin')

create the start of the current week on Monday in UTC for Europe/Berlin with:

TIMESTAMP(DATE_ADD(DATETIME_TRUNC(CURRENT_DATETIME('Europe/Berlin'),week),INTERVAL 1 day), 'Europe/Berlin')

create the start of the current months last year in UTC for Europe/Berlin with:

TIMESTAMP(DATETIME_SUB(DATETIME_TRUNC(CURRENT_DATETIME('Europe/Berlin'),month),INTERVAL 12 month),'Europe/Berlin')