Работа с Google BigQuery Date и Timestamp

SELECT CURRENT_DATETIME() AS now

SELECT
  FORMAT_DATETIME ('%Y/%d/%m', CAST ('2022-05-25 13:33:03' AS DATETIME)) AS day,
  FORMAT_DATETIME ('%A', CAST ('2022-05-25 13:33:03' AS DATETIME)) AS weekday,
  FORMAT_DATETIME ('%r', CAST ('2022-05-25 13:33:03' AS DATETIME)) AS time

SELECT
  CAST ('2022-05-25 11:33:04' AS DATETIME) AS datetime,
  CAST ('2022-05-25 11:33:04 Europe/Berlin' AS TIMESTAMP) AS timestamp,
  CAST ('2022-05-25' AS DATE) AS date,
  CAST ('11:33:04' as TIME) time

SELECT PARSE_DATE ('%Y%m%d', Cast (20220525 AS String)) AS datetime

SELECT PARSE_DATETIME ('%A %B %d, %Y %H:%M:%S','Wednesday May 25, 2022 13:40:10') AS parsed_datetime

SELECT TIMESTAMP_SECONDS (1574356800)

SELECT DATETIME_ADD ('2022-04-25', INTERVAL 6 MONTH);

SELECT
  CAST('2022-05-25 12:00:00' AS DATETIME) AS original_date,
  DATETIME_ADD(CAST('2022-05-25 12:00:00' AS DATETIME), INTERVAL 1 HOUR) AS one_hour_later,
  DATETIME_ADD(CAST('2022-05-25 12:00:00' AS DATETIME), INTERVAL 1 WEEK) AS one_week_later,
  DATETIME_ADD(CAST('2022-05-25 12:00:00' AS DATETIME), INTERVAL 1 QUARTER) AS one_quarter_later

SELECT
  *,
  DATE_ADD(CAST(product_send_start AS DATE), INTERVAL duration_month MONTH) AS product_coming
FROM
  (
    SELECT
      '2022-05-20' AS product_send_start,
      5 AS duration_month
    UNION ALL
(    SELECT
      '2021-12-18' AS product_send_start,
      12 AS duration_month)
    UNION ALL
(    SELECT
      '2022-06-12' AS product_send_start,
      25 AS duration_month)
  ) AS table_1

SELECT DATETIME_SUB (DATETIME "2022-05-20 16:42:05", INTERVAL 1 DAY) AS minus_day

SELECT
  CAST('2022-05-25 12:00:00' AS DATETIME) AS original_date,
  DATETIME_SUB(CAST('2022-05-25 12:00:00' AS DATETIME), INTERVAL 1 HOUR) AS one_hour_earlier,
  DATETIME_SUB(CAST('2022-05-25 12:00:00' AS DATETIME), INTERVAL 1 WEEK) AS one_week_earlier,
  DATETIME_SUB(CAST('2022-05-25 12:00:00' AS DATETIME), INTERVAL 1 QUARTER) AS one_quarter_earlier

SELECT
  due_arrival,
  FORMAT_DATE('%A', due_arrival) AS weekday,
  CASE WHEN EXTRACT(DAYOFWEEK FROM due_arrival) IN (2, 3) 
  THEN DATE_SUB(due_arrival, INTERVAL 5 DAY) 
  WHEN (EXTRACT(DAYOFWEEK FROM due_arrival) = 1) 
  THEN DATE_SUB(due_arrival, INTERVAL 4 DAY) 
  ELSE DATE_SUB(due_arrival, INTERVAL 3 DAY) END AS two_days_ago,
  FORMAT_DATE('%A', CASE WHEN EXTRACT(DAYOFWEEK FROM due_arrival) IN (2, 3) 
  THEN DATE_SUB(due_arrival, INTERVAL 5 DAY) 
  WHEN (EXTRACT(DAYOFWEEK FROM due_arrival) = 1) 
  THEN DATE_SUB(due_arrival, INTERVAL 4 DAY) 
  ELSE DATE_SUB(due_arrival, INTERVAL 3 DAY) END) AS weekday_2d_ago
FROM
  (
    SELECT
      CAST('2022-05-25' AS DATE) AS due_arrival
    UNION ALL
(    SELECT
      CAST('2021-09-20' AS DATE) AS due_arrival)
    UNION ALL
(    SELECT
      CAST('2022-12-15' AS DATE) AS due_arrival)
  ) AS table_1

SELECT 
TIMESTAMP_SUB(TIMESTAMP_SECONDS(CAST(SPLIT(client_id, '.')[OFFSET(1)] as INT)), INTERVAL 180 MINUTE) as f_unixtime,

SELECT DATE_TRUNC (DATE '2022-05-19', MONTH) AS first_day_of_month

SELECT
  CAST('2022-05-26' AS DATE) AS original_date,
  DATE_TRUNC(CAST('2022-05-26' AS DATE), WEEK) AS first_day_of_week,
  DATE_TRUNC(CAST('2022-05-26' AS DATE), MONTH) AS first_day_of_month,
  DATE_TRUNC(CAST('2022-05-26' AS DATE), YEAR) AS first_day_of_year

SELECT LAST_DAY (DATETIME '2022-05-25 14:30:20', WEEK(MONDAY))

SELECT
  CAST('2022-05-26' AS DATE) AS original_date,
  LAST_DAY(CAST('2022-05-26' AS DATE), WEEK) AS last_day_of_week,
  LAST_DAY(CAST('2022-05-26' AS DATE), MONTH) AS last_day_of_month,
  LAST_DAY(CAST('2022-05-26' AS DATE), YEAR) AS last_day_of_year

SELECT EXTRACT (MINUTE FROM DATETIME("2022-05-25 11:55:15")) AS minute;

SELECT
  date,
  EXTRACT(YEAR FROM date) AS year,
  EXTRACT(WEEK FROM date) AS week,
  EXTRACT(DAYOFWEEK FROM date) AS weekday
FROM
  UNNEST(GENERATE_DATE_ARRAY('2021-12-22', '2022-02-07')) AS date
ORDER BY
  date ASC

SELECT date
FROM
  (
    SELECT
      CAST('2022-05-10' AS DATE) AS date
    UNION ALL
(    SELECT
      CAST('2022-07-20' AS DATE) AS date)
    UNION ALL
(    SELECT
      CAST('2022-10-05' AS DATE) AS date)
    UNION ALL
(    SELECT
      CAST('2022-12-05' AS DATE) AS date)
  ) AS table_3
WHERE
  ((date >= '2021-05-10') AND (date < '2022-11-25'))

SELECT
  CURRENT_DATE () AS current_date,
  CURRENT_DATETIME () AS current_datetime,
  CURRENT_TIMESTAMP () AS current_timestamp,
  CURRENT_TIME () AS current_time

SELECT
 DATE "2022-10-10" as first_date,
 DATE "2022-05-10" as second_date,
 DATE_DIFF (DATE "2022-10-10",
   DATE "2022-05-10", MONTH) as month_difference;

WHERE date_diff(current_date(),date,DAY) <= X

SELECT
  date
FROM
  (
    SELECT
      CAST('2022-05-06' AS DATE) AS date
    UNION ALL
(    SELECT
      CAST('2022-09-08' AS DATE) AS date)
    UNION ALL
(    SELECT
      CAST('2021-03-02' AS DATE) AS date)
  ) AS table_1
WHERE
  (DATE_DIFF(CURRENT_DATE(), date, MONTH) < 10)

SELECT *
  FROM UNNEST (GENERATE_DATE_ARRAY ('2022-05-01', '2022-06-01', INTERVAL 1 DAY)) AS date

SELECT
         MY_DATE
        ,EXTRACT (YEAR FROM MY_DATE) as YEAR
        ,EXTRACT (MONTH FROM MY_DATE) as MONTH_NUMBER
        ,FORMAT_DATETIME ("%B", DATETIME(MY_DATE)) as MONTH
        ,EXTRACT (DAY FROM MY_DATE) as DAY_NUMBER
        ,EXTRACT (DAYOFWEEK FROM MY_DATE) as DAY_OF_WEEK
        ,EXTRACT (WEEK FROM MY_DATE) as WEEK_NUMBER
        ,EXTRACT (DAYOFYEAR FROM MY_DATE) as DAY_OF_YEAR
    FROM 
    (
    SELECT DATE_ADD ('2022-01-01', INTERVAL param DAY) AS MY_DATE
      FROM unnest (GENERATE_ARRAY (0, 1000, 1)) as param
    )

SELECT count (distinct user_id) as count_user_id
  from `fjshd-c61fd.analytics_263458172.events_*`
    where event_name = 'first_visit'
      and _TABLE_SUFFIX between '20220301' and '20220520'

WHERE
  _TABLE_SUFFIX = FORMAT_DATE ('%Y%m%d', DATE_SUB (CURRENT_DATE (), INTERVAL 1 DAY))