Menu
Suppose I have a query:
SELECT ga_channelGrouping, ga_sourceMedium, SUM(ga_sessionDuration)/SUM(ga_sessions) as avg_sessionDuration
FROM database.table
group by ga_channelGrouping, ga_sourceMedium
.
How do I select last week and this week's data from BigQuery if I have a DATE column which looks like this 2018-06-19 11:00:00 UTC.
583
WEEK(<WEEKDAY>) : Returns the week number of the date in the range [0, 53]. Weeks begin on WEEKDAY . Dates prior to the first WEEKDAY of the year are in week 0. Valid values for WEEKDAY are SUNDAY , MONDAY , TUESDAY , WEDNESDAY , THURSDAY , FRIDAY , and SATURDAY .
Subtracting two dates (Difference) Subtracting two dates from each other (i.e. calculating the difference) uses the DATE_DIFF function, where the second argument is subtracted from the first argument. The third argument is the unit (DATE_PART) in which you'd like to receive the calculation.
If you want to query data based on a time zone other than UTC, choose one of the following options: Adjust for time zone differences in your SQL queries. Use partition decorators to load data into specific ingestion-time partitions, based on a different time zone than UTC.
DATE_TRUNC is a useful function to get the beginning of the week and DATE_SUB gets you to last week
DATE_TRUNC
DATE_SUB
SELECT if(date(date) >= DATE_TRUNC(current_date(), WEEK(MONDAY)),"This Week","Last Week") weekPeriod,
ga_channelGrouping,
ga_sourceMedium,
SUM(ga_sessionDuration)/SUM(ga_sessions) as avg_sessionDuration
FROM database.table
WHERE date(date) >= DATE_SUB(DATE_TRUNC(current_date(), WEEK(MONDAY)), INTERVAL 1 WEEK)
group by weekPeriod, ga_channelGrouping, ga_sourceMedium
If your week starts on a Sunday, simply change WEEK(MONDAY) to WEEK(SUNDAY)
60
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
