CODEWITHSUNDEEP
sqlgoogle-bigquerybigdata
wizkids121
wizkids121

Reputation: 666

Best way to break down by weeks in BigQuery

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.

Upvotes: 5

Views: 17836

Answers (4)

Timur Mingulov
Timur Mingulov

Reputation: 2519

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

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

Upvotes: 6

Mig82
Mig82

Reputation: 5500

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

Upvotes: 2

pedrobin
pedrobin

Reputation: 191

I had to add parentheses:

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

Upvotes: 2

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173210

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')

Upvotes: 5

Related Questions