Reference

• Functions

  • date and time

    • date

    • time

      • timestamp

        • period of time difference

    • datetime

Period

until yesterday and previous three months

...

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

  AND TIMESTAMP(DATETIME_ADD(DATETIME_TRUNC(CURRENT_DATETIME('Europe/Berlin'),day),INTERVAL 24*60*60-1 second),'Europe/Berlin')

-390 days until -30 days 

WHERE datefield_utc

  BETWEEN

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

,INTERVAL 1 day),INTERVAL 30 + 360 day),'Europe/Berlin')

  AND

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

,INTERVAL 1 second),INTERVAL 30 day),'Europe/Berlin')

Time Difference

Offset Timestamp

You can add to timestamp to create future timestamps, you can subtract from  timestamp to create past timestamps.

TIMESTAMP_SUB()

create 2023-05-10 13:00:00 UTC with:

TIMESTAMP_SUB(TIMESTAMP('2023-05-17 13:00:00'),INTERVAL 7 day)

TIMESTAMP_ADD()

create 2023-05-24 13:00:00 UTC with:

TIMESTAMP_ADD(TIMESTAMP('2023-05-17 13:00:00'),INTERVAL 7 day)

Length of Period

• DATETIME_DIFF(DATETIME(LATER_DATE),DATETIME(EARLIER_DATE),...) >>> makes a positive output

• DATETIME_DIFF(DATETIME(EARLIER_DATE),DATETIME(LATER_DATE),...) >>> makes a negative output

You can calculate the period between two points of time with timestamp_diff in days down to microseconds.

TIMESTAMP_DIFF() Don't forget to set the date part at the end!

create 3 with:

TIMESTAMP_DIFF(TIMESTAMP('2021-03-04 23:59:59'),TIMESTAMP('2021-03-01 00:00:00'),day)

create -3 with:

TIMESTAMP_DIFF(TIMESTAMP('2021-03-01 00:00:00'),TIMESTAMP('2022-03-04 23:59:59'),day)

create 1 with:

TIMESTAMP_DIFF(TIMESTAMP('2023-01-31 02:20:20'),TIMESTAMP('2023-01-31 01:20:20'),hour)

create 0 with:

TIMESTAMP_DIFF(TIMESTAMP('2023-01-31 02:20:10')TIMESTAMP('2023-01-31 01:20:20'),hour)

If you want to calculated time difference beyond days, you will have to switch to datetime vaues and datetime_diff.

create 1 with:

DATETIME_DIFF(DATETIME('2021-04-30 23:59:59'),DATETIME('2021-03-01 00:00:00'),month)

create 2 with:

DATETIME_DIFF(DATETIME('2021-05-01 23:59:59'),DATETIME('2021-03-01 00:00:00'),month)

create -2 with:

DATETIME_DIFF(DATETIME('2021-03-01 23:59:59'),DATETIME('2021-05-01 00:00:00'),month)

Decimal Days

create 0.25 days with:

TIMESTAMP_DIFF(TIMESTAMP('2024-03-22 03:00:00'),TIMESTAMP('2024-03-21 21:00:00'), second)/(24*60*60)