How to Query Date and Time in BigQuery

select current_timestamp; -- date and time, with timezone
select current_datetime; -- date and time, without timezone
select current_date; -- date
select current_time; -- time

select *
from table
where created_at >= timestamp1
and created_at < timestamp2

select *
from table
where timestamp > timestamp_sub(current_timestamp, INTERVAL 7 DAY);

select *
from table
where timestamp > timestamp_sub(current_timestamp, INTERVAL 14 DAY)
and timestamp_sub(current_timestamp, INTERVAL 7 DAY);

select extract(minute from timestamp); --or datetime, date, time

--returns 1-7, where 1 is Sunday and 7 is Saturday.
select extract(dayofweek from timestamp);

-- returns a string like Monday, Tuesday, etc
select format_timestamp('%A',timestamp); --

select unix_seconds(current_timestamp);
select unix_seconds(timestamp('2020-05-09 14:53:21'));

 -- timestamp
select timestamp_diff(timestamp2, timestamp1, SECOND);

-- datetime
select datetime_diff(datetime2, datetime1, SECOND);

-- date
select date_diff(date2, date1, DAY);

-- time
select time_diff(time2, time1, SECOND);