Solved: Extract HOUR - cloud spanner SQL fails

srinimk · 11-13-2023 12:47 AM

Hi,

I was trying to extract the hour of a timestamp value and the query returned wrong value

select extract(HOUR from CURRENT_TIMESTAMP) returns zero.

However,

SELECT EXTRACT(HOUR FROM TIMESTAMP '2023-11-12T10:38:30.465736119Z'), CURRENT_TIMESTAMP

returns

2

2023-11-13T08:44:27.143546596Z

It looks like it subtracts the hour from the current hour and returns the value. This is not the case with other parameters in the extract (like Day, minute, year etc).

Is there something wrong on my side. OR is this an issue ?

ms4446

In Spanner, the EXTRACT() function plays a crucial role in extracting specific date or time components from timestamps. However, it's essential to understand how time zones are handled in this function and the CURRENT_TIMESTAMP keyword.

EXTRACT() Function and Time Zones

The EXTRACT() function operates on timestamps, which inherently carry time zone information. When extracting a date or time component, it considers the timestamp's time zone and returns the corresponding value.

For instance, consider the query:

EXTRACT(HOUR FROM TIMESTAMP '2023-11-12T10:38:30.465736119Z')

This query extracts the hour from a timestamp specified in UTC format. The 'Z' at the end indicates UTC, and the function returns 10, which is the hour part of the timestamp in UTC.

Local Time Zone Extraction

To extract a date or time component from a timestamp in a specific time zone, you can explicitly specify the time zone using the AT TIME ZONE clause. For example, to convert the UTC timestamp above to the 'America/New_York' time zone and then extract the hour:

EXTRACT(HOUR FROM TIMESTAMP '2023-11-12T10:38:30.465736119Z' AT TIME ZONE 'America/New_York')

This query converts the UTC timestamp to Eastern Time and then extracts the hour from the converted time.

CURRENT_TIMESTAMP and UTC

It's important to note that CURRENT_TIMESTAMP in Google Cloud Spanner always returns the current time in UTC, not the local time zone of the query execution environment. Therefore, if you extract the hour from CURRENT_TIMESTAMP, it will be the hour in UTC.

Key Takeaways

The EXTRACT() function extracts date or time components from timestamps, considering their time zones.
To extract components from timestamps in a specific time zone, use the AT TIME ZONE clause.
CURRENT_TIMESTAMP always returns the current time in UTC, regardless of the query's local time zone.

Remember, time zones play a significant role in timestamp interpretation. Always consider the time zone context when working with timestamps in databases to avoid discrepancies and ensure accurate results.

View solution in original post

ms4446