Date & Time Functions

SELECT CURRENT_DATE -- '2024-01-15'
SELECT user_id, email, event_date FROM company_data."123" WHERE event_date = CURRENT_DATE

SELECT CURRENT_TIMESTAMP -- '2024-01-15 14:30:00'

SELECT DATE_ADD('2024-01-01', 7) -- '2024-01-08'
SELECT DATE_ADD(start_date, 30) AS end_date FROM company_data."123"

SELECT DATE_SUB(CURRENT_DATE, 7) -- 7 days ago

SELECT DATEDIFF('2024-01-10', '2024-01-01') -- 9
SELECT DATEDIFF(end_date, start_date) AS duration FROM company_data."123"

SELECT DATE_TRUNC('day', '2024-01-15 14:30:00')   -- '2024-01-15 00:00:00'
SELECT DATE_TRUNC('month', '2024-01-15 14:30:00') -- '2024-01-01 00:00:00'
SELECT DATE_TRUNC('year', '2024-01-15 14:30:00')  -- '2024-01-01 00:00:00'

SELECT EXTRACT(YEAR FROM '2024-01-15')  -- 2024
SELECT EXTRACT(MONTH FROM '2024-01-15') -- 1
SELECT EXTRACT(DAY FROM '2024-01-15')   -- 15
SELECT EXTRACT(HOUR FROM '2024-01-15 14:30:00') -- 14

SELECT TO_DATE('2024-01-15') -- DATE '2024-01-15'
SELECT TO_DATE(date_string) FROM company_data."123"

SELECT TO_TIMESTAMP('2024-01-15 10:30:00')

SELECT FROM_UNIXTIME(1705312200) -- '2024-01-15 10:30:00'
SELECT FROM_UNIXTIME(epoch_seconds) AS event_time FROM company_data."123"

SELECT UNIX_TIMESTAMP('2024-01-15 10:30:00') -- 1705312200
SELECT UNIX_TIMESTAMP(event_timestamp) AS epoch FROM company_data."123"

SELECT
  FORMAT_TIMESTAMP('YYYY-MM-DD', timestamp_col) AS date_only,
  FORMAT_TIMESTAMP('HH24:MI:SS', timestamp_col) AS time_only,
  FORMAT_TIMESTAMP('YYYY-MM-DDTHH24:MI:SS', timestamp_col) AS iso_no_tz,
  FORMAT_TIMESTAMP('YYYY-MM-DDTHH24:MI:SS TZH', timestamp_col) AS iso_with_tz,
  FORMAT_TIMESTAMP('YYYY-MM-DDTHH24:MI:SS TZH:TZM', timestamp_col) AS iso_with_full_tz
FROM company_data."123"