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!)

zetasql/zetasql/reference_impl/evaluation.cc

Line 126 in 177d495

ZETASQL_CHECK(absl::LoadTimeZone("America/Los_Angeles", &timezone));

It is possible to set the default timezone on the analyzer using the AnalyzerOptions

zetasql/zetasql/public/analyzer_options.h

Line 469 in 177d495

void set_default_time_zone(absl::TimeZone timezone) {

zetasql/java/com/google/zetasql/AnalyzerOptions.java

Line 249 in 177d495

public void setDefaultTimezone(String timezone) {

[marcosapmf]

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