References:
Date Functions
https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions
Format Elements
https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_elements_date_time
Current Date, Last_Date, Construction Add and Subtract
CURRENT_DATE() as Current_Date, -- returns todays date
DATE(2024,01,24) as Constructed_Date, -- constructs the date from Year, Month, Day
LAST_DAY('2008-12-25') AS Last_Date, -- returns 2008-12-25 the last date
DATE_ADD(DATE "2024-01-24", INTERVAL 1 DAY), -- 1 day later
DATE_SUB(DATE "2024-01-24", INTERVAL 1 DAY), -- 1 day before
FORMAT_DATE and PARSE_DATE
SELECT
CURRENT_DATE() as today, -- returns today
FORMAT_DATE('%d/%m/%Y', CURRENT_DATE()) AS formatteday, -- UK format
PARSE_DATE('%d-%m-%Y', '01-05-2024') as passstringdate, -- convert string to date object
FORMAT_DATE('%d/%m/%Y', PARSE_DATE('%d-%m-%Y', '01-05-2024')) AS formatedpassstring -- convert string to date and then format
EXTRACT
--CURRENT_DATE() is 15/05/2024
SELECT
EXTRACT(DAY from CURRENT_DATE()) as day, -- 15/05/2024
EXTRACT(DAYOFWEEK from CURRENT_DATE()) as DayOfWeek, -- 4
EXTRACT(WEEK from CURRENT_DATE()) as Week, --19
EXTRACT(WEEK(MONDAY) from CURRENT_DATE()) as WeekStartingMonday, -- 20
EXTRACT(ISOWEEK from CURRENT_DATE()) as WeekStartingMonday2, -- 20
EXTRACT(MONTH from CURRENT_DATE()) as Mth, -- 5
EXTRACT(QUARTER from CURRENT_DATE()) as Qtr, -- 2
EXTRACT(YEAR from CURRENT_DATE()) as Yr, -- 2024
EXTRACT(ISOYEAR from CURRENT_DATE()) as YrWkStartMon -- 2024
DATE_DIFF
SELECT DATE_DIFF(DATE '2024-01-25', DATE '2024-01-02', DAY) AS days_diff -- returns 23 (note format end_date, start_date)DATE() Constructor
SELECT *
FROM `bigquery-public-data.google_trends.top_terms`
WHERE rank = 1
--Date Constructor
AND refresh_date >= DATE(2024, 01, 01)
LIMIT 10
DATE TRUNC()
SELECT *
FROM `bigquery-public-data.google_trends.top_terms`
WHERE rank = 1
-- DATE_SUB subtracts from given date
AND refresh_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 2 WEEK)
-- Limit result to 10 (works like TOP)
LIMIT 10
Leave a Reply