[jira] [Commented] (CALCITE-6045) CURRENT_TIMESTAMP has incorrect return type
[
https://issues.apache.org/jira/browse/CALCITE-6045?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=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)
[jira] [Commented] (CALCITE-6045) CURRENT_TIMESTAMP has incorrect return type
[
https://issues.apache.org/jira/browse/CALCITE-6045?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17775934#comment-17775934
]
Tanner Clary commented on CALCITE-6045:
---
For FLOOR and CEIL, we added some logic to the parser that checks the current
conformance and if it is BQ, then use the BQ-specific operator. I think Jerin
is dealing with something similar and a lot of the suggestions (that I have
had, at least) involve checking the conformance. Then maybe you could have an
operator like CURRENT_TIMESTAMP_BQ or something similar. See FLOOR/CEIL like I
mentioned or SUBSTR. If there's another difference between the operators, like
operand count, which I don't think is applicable here, you could also use that
instead.
> 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)
[jira] [Commented] (CALCITE-6045) CURRENT_TIMESTAMP has incorrect return type
[
https://issues.apache.org/jira/browse/CALCITE-6045?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17775930#comment-17775930
]
Will Noble commented on CALCITE-6045:
-
This would seem to be a case where the standard SQL function
{{CURRENT_TIMESTAMP}} (meant to return a {{{}TIMESTAMP WITH TIME ZONE}}) has a
name collision with the BigQuery-specific function {{CURRENT_TIMESTAMP}} (mean
to return a {{{}TIMESTAMP WITH LOCAL TIME ZONE}} a.k.a. BigQuery-specific
{{TIMESTAMP}}). Is there a standard procedure for handling function name
collisions between standard SQL and particular dialects?
> 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)
[jira] [Commented] (CALCITE-6045) CURRENT_TIMESTAMP has incorrect return type
[
https://issues.apache.org/jira/browse/CALCITE-6045?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17774265#comment-17774265
]
Julian Hyde commented on CALCITE-6045:
--
This all sounds right. I have been feeling guilty for several years that
{{CURRENT_TIMESTAMP}} returns a {{TIMESTAMP}} rather than the {{TIMESTAMP WITH
TIME ZONE}} as required by the SQL standard.
We should fix it, but it will be a breaking change. Also, do you think the
{{TIMESTAMP WITH TIME ZONE}} data type is in good enough shape to support it?
Note that there is also {{LOCAL_TIMESTAMP}}, which I believe is of type
{{TIMESTAMP}} and is in the session time zone. I believe that its type and
value are correct.
Should we add a new function that returns a {{TIMESTAMP WITH LOCAL TIME ZONE}}?
> 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)
