Cloud Spanner Postgres Dialect - Timestamps type cast to String Issue

[sharmarahul-ece]

Context: I have stored timestamp value in UTC. I want to type cast it string using `to_char()` function.

Issue: While converting the date to string, it adds time zone conversion of local time. And do not find an option to stop or disable it. 

Sample Query: 

SELECT to_char(eventdate, 'yyyy-mm-dd') AS KEY
FROM
  public.my_table

Value in database: 2024-09-01T00:00:00Z 

Result of query: 2024-08-31

Expected Result: 2024-09-01 

[ms4446]

In Spanner, when formatting UTC TIMESTAMP values to strings using TO_CHAR() without specifying a time zone, the function defaults to the database's default time zone, which is typically America/Los_Angeles. This default behavior can lead to unintended date shifts, such as converting '2024-09-01T00:00:00Z' to '2024-08-31' due to the 7-hour time difference between UTC and Pacific Daylight Time.

To ensure accurate representation of UTC timestamps, explicitly specify the time zone in your formatting functions. Here are two effective methods:

1. Using TO_CHAR() with AT TIME ZONE Convert the timestamp to UTC before formatting:

   SELECT TO_CHAR(eventdate AT TIME ZONE 'UTC', 'YYYY-MM-DD') AS KEY FROM public.my_table;
   

   This approach ensures that the eventdate is interpreted in UTC, preventing any unintended time zone conversions during formatting.

2. Using FORMAT_TIMESTAMP() Format the timestamp directly in UTC:

   SELECT FORMAT_TIMESTAMP('%F', eventdate, 'UTC') AS KEY FROM public.my_table;
   

[sharmarahul-ece]

Thank you @ms4446 for your reply. 
Kindly note: We are using postgresql dialect. And spanner studio is giving syntax error. Refer to the given below snapshot.

[ms4446]

Thanks for the clarification and proving the screenshot. Since you're using Spanner with the PostgreSQL dialect, this changes things. The below options should work:

Option 1. Using TO_CHAR() with AT TIME ZONE

SELECT TO_CHAR((eventdate AT TIME ZONE 'UTC'), 'YYYY-MM-DD') AS KEYFROM public.exposition
LIMIT 1;

This ensures eventdate is interpreted in UTC wall time before formatting.

The parentheses around eventdate AT TIME ZONE 'UTC' are required due to function argument precedence in SQL.

Option 2. Using FORMAT_TIMESTAMP() (GoogleSQL-style, but supported in PostgreSQL dialect)

SELECT FORMAT_TIMESTAMP('%Y-%m-%d', eventdate, 'UTC') AS KEYFROM public.exposition
LIMIT 1;

This method is often clearer and more flexible for timestamp formatting.

%Y-%m-%d corresponds to the YYYY-MM-DD format.