Reputation: 666
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
Reputation: 2519
The documentation advises using standard SQL functions, like DATE_TRUNC():
SELECT DATE_TRUNC(DATE '2019-12-25', WEEK) as week;
Upvotes: 6
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 thedatetime_expression
.ISOWEEK
s begin on Monday. Return values are in the range [1, 53]. The firstISOWEEK
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
Reputation: 191
I had to add parentheses:
SELECT DATE_TRUNC(DATE('2016-04-06 20:58:06 UTC'), WEEK) as week;
Upvotes: 2
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