I raised a bug with BigQuery support. It took months for them to get back to me. Then they clearly did not bother to read the bug report and gave me responses that are completely irrelevant, then closed the bug ticket. This is extremely frustrating. I have to question the culture of the BigQuery engineering team and the quality of product.
Original bug report in April:
Expected behaviour: "WEEK" as an acceptable date_part, as per documentation
https://cloud.google.com/bigquery/docs/reference/standard-sql/datetime_functions#datetime_diff
Actual behaviour: received this error "DATETIME_DIFF does not support the WEEK date part at [23:3]"
Example code:
datetime_diff(timestamp_millis(cast(events.createdAt as INT64)), timestamp_millis(cast(fv.startdate as INT64)), week)
(events.createdAt and fv.startdate are unix timestamps stored as numbers, hence had to be casted and converted first).
I have also tried "DATE_DIFF" instead of "DATETIME_DIFF" and experienced same error.
First response from Google, in June:
Since you are using the timestamp function, instead of using WEEK, you can use DAY as date_part. However, you will need to convert the day count into week count according to your needs.
You can try the below query for your requirement :
datetime_diff(timestamp_millis(cast(events.createdAt as INT64)), timestamp_millis(cast(fv.startdate as INT64)), day) // And then use the day count to calculate the weeks
I would recommend you to convert the timestamp to dates before, so that you can use the WEEK as date_part.
Please follow the above workaround, and let me know if it works for you or not?
This response ignores the fact that "WEEK" is listed in their documentation as an acceptable PART. I know the workarounds, that's not what I'm after. Besides, any business critical application would not be able to wait 2 months for an answer.
Second response from Google in August:
Status: Won't Fix (Intended Behavior)
Hello,
The issue is with using a date function with timestamp values.
Since date_diff
is a date function, your values must be dates.
For example:
select date_diff(date(current_timestamp()), date_add(date(current_timestamp()), interval -14 day), WEEK)
I will proceed to close this issue as Won’t Fix(Intended behavior).
However, if you still face any issue, please don’t hesitate to raise a new thread describing the issue.
My original bug ticket was raised against the DATETIME_DIFF function, not DATE_DIFF
They clearly did not bother to read the ticket. Such appallingly disrespectful behaviour.