Timestamp with no time zone id is returning a different value

[marcosapmf]

Hi! I'm trying to analyze a simple timestamp value (i.e SELECT TIMESTAMP '2019-02-11 20:26:53.218';) with no time zone id by using the function AnalyzeNextStatement. After the analysis, the output has changed the timestamp value to 2019-02-12 04:26:53.218+00. Shouldn't it be 2019-02-11 20:26:53.218+00 instead?

Is there any way to parameterize the time zone id without passing it within the timestamp?

Thanks in advance!

[nielm]

2019-02-12 04:26:53.218+00 (ie UTC) is the same as 2019-02-11 20:26:53.218+08:00 (ie US - Pacific time)

This would make sense if the local time zone is US-Pacific on the machine you run this on, as it would assume a timestamp without timezone is expressed in 'local time'... (and IMHO is a reason why timestamps should always have a timezone to avoid ambiguity).

[marcosapmf]

Hi @nielm, thanks for answering! That was one of the first things I thought. However I'm running this on my local machine which is in Brazil time (GMT-3), so if it's using the local time zone the result should have been 2019-02-11 23:26:53.218+00, which is 2019-02-11 20:26:53.218+03:00, right?

[nielm]

Looking at the code, it initializes a default time zone to America/Los_Angeles (US-Pacific) (which is very normal for a Google product!) https://github.com/google/zetasql/blob/177d495a064e38684c462cf883e22428273bd996/zetasql/reference_impl/evaluation.cc#L126

It is possible to set the default timezone on the analyzer using the AnalyzerOptions https://github.com/google/zetasql/blob/177d495a064e38684c462cf883e22428273bd996/zetasql/public/analyzer_options.h#L469 https://github.com/google/zetasql/blob/177d495a064e38684c462cf883e22428273bd996/java/com/google/zetasql/AnalyzerOptions.java#L249

[marcosapmf]

I see. I'll try to use it. Thanks a lot @nielm !