Best way to break down by weeks in BigQuery

Question

So what I'm looking to do is create a report that shows how many sales a company had on a weekly basis.

So we have a time field called created that looks like this:

2016-04-06 20:58:06 UTC

This field represents when the sale takes place.

Now lets say I wanted to create a report that gives you how many sales you had on a weekly basis. So the above example will fall into something like Week of 2016-04-03 (it doesn't have to exactly say that, I'm just going for the simplest way to do this)

Anyone have any advice? I imagine it involves using the UTEC_TO_xxxxxx functions.

Answer 1

you can use WEEK() function - it gives you week number

SELECT WEEK('2016-04-06 20:58:06 UTC')

if you need first day of the week - you can try something like

STRFTIME_UTC_USEC((UTC_USEC_TO_WEEK(TIMESTAMP_TO_USEC(TIMESTAMP('2016-05-02 20:58:06 UTC')), 0)),'%Y-%m-%d')

Answer 2

The documentation advises to use standard SQL functions, like DATE_TRUNC():

SELECT DATE_TRUNC(DATE '2019-12-25', WEEK) as week;

Answer 3

I had to add parentheses:

SELECT DATE_TRUNC(DATE('2016-04-06 20:58:06 UTC'), WEEK) as week;

Answer 4

This is quite an old question and things have moved on since.

In my case, I found that the old WEEK function is no longer recognised, so I had to instead use the EXTRACT function. The doc for it can be found here.

For me it was enough to just extract the ISOWEEK from the timestamp, which results in the week of the year (the ISOYEAR) as a number.

ISOWEEK: Returns the ISO 8601 week number of the datetime_expression. ISOWEEKs begin on Monday. Return values are in the range [1, 53]. The first ISOWEEK of each ISO year begins on the Monday before the first Thursday of the Gregorian calendar year.

So I did this:

SELECT EXTRACT(ISOWEEK FROM created) as week

And if you want to see the week's last day, rather than the week's number in a year, then:

SELECT last_day(datetime(created), isoweek) as week