[ https://issues.apache.org/jira/browse/CALCITE-6045?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17775977#comment-17775977 ]
Julian Hyde commented on CALCITE-6045: -------------------------------------- I think that Calcite's {{CURRENT_TIMESTAMP}} function should have type {{TIMESTAMP WITH LOCAL TIME ZONE}}, which means that (after type alias translation) it will have the requisite type for BigQuery (what BigQuery calls {{TIMESTAMP}}). (That is a change to current behavior, and a departure from the ISO standard, but still the best type, in my opinion.) If people want a {{TIMESTAMP}} (what BigQuery calls {{DATETIME}}) they can call {{LOCALTIMESTAMP}}, whose behavior will be unchanged. > CURRENT_TIMESTAMP has incorrect return type > ------------------------------------------- > > Key: CALCITE-6045 > URL: https://issues.apache.org/jira/browse/CALCITE-6045 > Project: Calcite > Issue Type: Bug > Reporter: Tanner Clary > Priority: Major > > When trying to work on CALCITE-6021, I noticed that {{CURRENT_TIMESTAMP}} > currently returns type {{TIMESTAMP}} when it should be > {{TIMESTAMP_WITH_LOCAL_TIME_ZONE}}. > After modifying it, I noticed function was returning the time from (UTC - > System TZ) hours ago. For example, I am in {{America/Los_Angeles}} and if I > called the function at {{2023-10-10 13:28:00 America/Los_Angeles}}, it would > return {{2023-10-10 06:28:00 America/Los_Angeles}}. > I think this is because the DataContext {{CURRENT_TIMESTAMP}} variable, which > is meant to represent milliseconds since epoch UTC, actually has the timezone > offset applied in {{CalciteConnectionImpl#DataContextImpl}} > [here|https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/jdbc/CalciteConnectionImpl.java#L442]. > To be clear: it is meant to represent millis since epoch UTC, but instead it > is millis since epoch [system tz], as I understand it. > Additionally, I believe the {{getString()}} method for timestamps in > AvaticaResultSet should behave similarly to > [{{SqlFunctions#timestampWithLocalTimezoneToString()}}|https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java#L4021] > when dealing with a {{TIMESTAMP WITH LOCAL TIME ZONE}}. Right now, it does > not take the timezone into consideration so although it represents the > accurate instant in time, it displays differently than > {{CAST(CURRENT_TIMESTAMP AS VARCHAR)}}. > For example, {{SELECT CURRENT_TIMESTAMP, CAST(CURRENT_TIMESTAMP AS > VARCHAR)}}, with the correct return type, returns something like: > {{2023-10-10 13:28:00 | 2023-10-10 06:28:00.000 America/Los_Angeles}} -- This message was sent by Atlassian Jira (v8.20.10#820010)