Assigned
Status Update
No update yet.
Comments
va...@google.com
si...@google.com
Hello,
Thank you for reaching out to us with your request.
We have duly noted your feedback and will thoroughly validate it. While we cannot provide an estimated time of implementation or guarantee the fulfillment of the issue, please be assured that your input is highly valued. Your feedback enables us to enhance our products and services.
We appreciate your continued trust and support in improving our Google Cloud Platform products. In case you want to report a new issue, Please do not hesitate to create a new issue on the
Once again, we sincerely appreciate your valuable feedback; Thank you for your understanding and collaboration.
Description
The documentation for TIMESTAMP_DIFF does not explicitly mention what the behavior is when the type of arguments is TIMESTAMP vs DATE. It says "The behavior of the this function follows the type of arguments passed in.", but then both TIMESTAMP_DIFF and DATE_DIFF say they "Gets the number of unit boundaries" between the two input values.
When TIMESTAMP_DIFF is applied to TIMESTAMP values with a granularity of MINUTE, it will return 0 for diff <59 seconds.
When TIMESTAMP_DIFF is applied to DATE values with a granularity of MINUTE, it will return 0 or 1 depending on whether the two dates are on different sides of the minute boundary, regardless of the absolute value of the diff.
Here is a concrete example where UTC vs local time has an unexpected (and undocumented) difference, 0 vs 1 for timestamp_diff:
[{
"actual_start_timestamp": "2025-01-26 16:14:02.924034 UTC",
"scheduled_start_timestamp": "2025-01-26 16:13:32.912992 UTC",
"timestamp_diff_utc": "0",
"actual_start_local": "2025-01-26T08:14:02.924034",
"scheduled_start_local": "2025-01-26T08:13:32.912992",
"timestamp_diff_local": "1"
}, {
"actual_start_timestamp": "2025-01-06 20:50:52.677843 UTC",
"scheduled_start_timestamp": "2025-01-06 20:50:22.623514 UTC",
"timestamp_diff_utc": "0",
"actual_start_local": "2025-01-06T12:50:52.677843",
"scheduled_start_local": "2025-01-06T12:50:22.623514",
"timestamp_diff_local": "0"
}]
Below is how the two diffs were computed.
timestamp_diff(
actual_start_timestamp,
scheduled_start_timestamp,
minute) as timestamp_diff_utc,
timestamp_diff(
actual_start_local,
scheduled_start_local,
minute) as timestamp_diff_local
What you expected to happen:
The documentation needs to explicitly explain that the "unit boundary" definition is different for TIMESTAMP vs DATE, and show a concrete example for it both in TIMESTAMP_DIFF and DATE_DIFF pages.