Reference

• Functions

  • date and time

    • date

    • time

      • timestamp

        • subtract time 

        • add time

    • datetime

Now

create the current timestamp in UTC with:

CURRENT_TIMESTAMP()

create the current timestamü (without miliseconds) for Europe/Berlin as:

TIMESTAMP_TRUNC(CURRENT_DATETIME('Europe/Berlin'),SECOND)

create the current datetime for Europe/Berlin as:

DATETIME_TRUNC(CURRENT_DATETIME('Europe/Berlin'),SECOND) AS current_timestamp_tzde

Query Time Stamp

create query timestamp:

DATETIME_TRUNC(CURRENT_DATETIME('Europe/Berlin'),second) AS query_timestamp_tzde

Days

Day

TIMESTAMP_SUB(TIMESTAMP(CURRENT_DATE('Europe/Berlin'),'Europe/Berlin'), INTERVAL 1 second) AS yesterday_last_second

Create End of Day

DATETIME_ADD(DATETIME_TRUNC(DATETIME(with_my_selections.date_value,'Europe/Berlin'),day),INTERVAL 24*60*60-1 second) AS date_value_tzde

Work Day

SELECT

  FORMAT_DATETIME('%a %F %T',CURRENT_DATE('Europe/Berlin')) AS current_day_tzde,

  MOD(LEAST(3,EXTRACT(DAYOFWEEK FROM

        CURRENT_DATE('Europe/Berlin'))),3)+1 AS off_set_for_last_previous_working_day,

  FORMAT_DATETIME('%a %F %T',DATE_SUB(CURRENT_DATE('Europe/Berlin'),INTERVAL MOD(LEAST(3,EXTRACT(DAYOFWEEK FROM 

            CURRENT_DATE('Europe/Berlin'))),3)+1 day)) AS last_previous_working_day_tzde ;

timestamps UTC of previous work day

SELECT

  TIMESTAMP(DATE_SUB(CURRENT_DATE('Europe/Berlin'),INTERVAL MOD(LEAST(3,EXTRACT(DAYOFWEEK FROM 

            CURRENT_DATE('Europe/Berlin'))),3)+1 day),'Europe/Berlin'),

  TIMESTAMP_ADD(TIMESTAMP(DATE_SUB(CURRENT_DATE('Europe/Berlin'),INTERVAL MOD(LEAST(3,EXTRACT(DAYOFWEEK FROM 

              CURRENT_DATE('Europe/Berlin'))),3)+1 day),'Europe/Berlin'),INTERVAL 24*60*60-1 second)

Day of Week

see all format elements

SELECT

  FORMAT_DATETIME('%u', DATETIME('2024-04-01 11:00:00')) AS get_string_1,

  EXTRACT(dayofweek FROM DATETIME('2024-04-01 11:00:00')) AS get_int_2;

Find Preferred Off-Set Day

Find -3 for Mon, -2 for Sun, all other weekdays -1:

SELECT

IF

  (EXTRACT(dayofweek

    FROM

      CURRENT_DATE('Europe/Berlin'))>2

,1

,1 + EXTRACT(dayofweek FROM CURRENT_DATE('Europe/Berlin')))

*(-1);

Week

Begin of Week

get the first day of a week  i.e. Monday viz. begin of ISO week.

DATETIME_TRUNC(CURRENT_DATETIME('Europe/Berlin'),isoweek) AS date_bow

End of Week

get the last second of Sunday of a Week viz. end of ISO week.

DATETIME_ADD(DATETIME_TRUNC(CURRENT_DATETIME('Europe/Berlin'),isoweek),INTERVAL 7*24*60*60-1 second) AS date_eow

Month

• TIMESTAMP(DATE_TRUNC(CURRENT_DATE('Europe/Berlin'), month),'Europe/Berlin') AS begin_of_current_month

• TIMESTAMP_ADD(TIMESTAMP(LAST_DAY(CURRENT_DATE('Europe/Berlin'), month),'Europe/Berlin'), INTERVAL 24*60*60-1 second) AS end_of_curren_month

• TIMESTAMP(DATE_SUB(DATE_TRUNC(CURRENT_DATE('Europe/Berlin'), month), INTERVAL 1 month),'Europe/Berlin') AS begin_of_last_month,

• TIMESTAMP_SUB(TIMESTAMP(DATE_TRUNC(CURRENT_DATE('Europe/Berlin'), month),'Europe/Berlin'), INTERVAL 1 second) AS end_of_last_month

• TIMESTAMP(DATE_SUB(CURRENT_DATE('Europe/Berlin'), INTERVAL 6 month),'Europe/Berlin') AS today_six_month_before_utc

End of Month Day

• LAST_DAY

e.g.

LAST_DAY(DATETIME(p.completed_at,'Europe/Berlin'),MONTH)

DATETIME_ADD(DATETIME(LAST_DAY(DATE(purchases.completed_at,'Europe/Berlin'),month)),INTERVAL 24*60*60-1 second) AS date_eom_purchase_completed_tzde

Year

TIMESTAMP(DATE_SUB(DATE_TRUNC(CURRENT_DATE('Europe/Berlin'), year), INTERVAL 12 month),'Europe/Berlin') AS begin_of_last_year

Date List Array

WITH

  bom_array AS (

  SELECT

    GENERATE_DATE_ARRAY( SAFE_CAST(FORMAT_TIMESTAMP('%Y-%m-01',MIN(t.created_at)) AS DATE), SAFE_CAST(FORMAT_TIMESTAMP('%Y-%m-01',MAX(t.created_at)) AS DATE),INTERVAL 1 month) AS col

  FROM

    `project.dataset.table` AS t)

SELECT

  TIMESTAMP(bom_date,'Europe/Berlin') AS bom_date_utc,

  TIMESTAMP(DATETIME_SUB(DATETIME_ADD(SAFE_CAST(bom_date AS datetime),INTERVAL 1 month), INTERVAL 1 second),'Europe/Berlin') AS eom_date_utc

FROM

  bom_array,

  UNNEST(bom_array.col) AS bom_date;