BigQuery Standard SQL returns different WEEK from timestamp

Question

I am converting an old query from Legacy SQL to Standard SQL and noticed my report was off. I've traced it back to Legacy SQL and Standard SQL returning different weeks based off of a UNIX millisecond timestamp.

I was under the impression the Legacy SQL query was correct, but however, I'm curious to know the difference. It's not across all weeks, but enough to throw my report off significantly.

Here's an example:

#legacySQL
SELECT WEEK(MSEC_TO_TIMESTAMP(1470631859000)) AS sign_up_week;

Output: 33

#standardSQL
SELECT EXTRACT(WEEK FROM TIMESTAMP_MILLIS(1470631859000));

Output: 32

I've viewed the following documentation on the Legacy SQL reference:

WEEK(<timestamp>)
Returns the week of a TIMESTAMP data type as an integer between 1 and 53, inclusively.

Weeks begin on Sunday, so if January 1 is on a day other than Sunday, week 1 has fewer than 7 days and the first Sunday of the year is the first day of week 2.

And from the Standard SQL reference:

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.

ISOWEEK: Returns the ISO 8601 week number of the date_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.

How can I get my Standard SQL query to output the same week number as my Legacy SQL query? If not, which week number is correct? It does not appear I can get them to dovetail natively.

Gordon Linoff Gordon Linoff · Accepted Answer · 2018-11-10T15:36:30

This is a bit subtle, but legacy and standard SQL handle weeks differently. In Legacy SQL:

Weeks begin on Sunday, so if January 1 is on a day other than Sunday, week 1 has fewer than 7 days and the first Sunday of the year is the first day of week 2.

In Standard SQL:

WEEK: Returns the week number of the date in the range [0, 53]. Weeks begin with Sunday, and dates prior to the first Sunday of the year are in week 0.

So, in Legacy SQL the first Sunday is week 2. In Standard SQL, the first Sunday is week 1.

BigQuery Standard SQL returns different WEEK from timestamp

1 Answers