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))