[
https://issues.apache.org/jira/browse/CALCITE-5301?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17613759#comment-17613759
]
Almog Gavra commented on CALCITE-5301:
--------------------------------------
[~julianhyde] I think your understanding of {{AT TIME ZONE}} vs {{WITH TIME
ZONE}} is exactly how I understand it.
In my local prototyping I've been using Postgres as the model to follow
([https://www.postgresql.org/docs/current/datatype-datetime.html] and
[https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT)].
We implemented it as a SqlBinaryOperator with the same precedence as {{LIKE}}
(though it'll be cleaner to integrate it directly into the Calcite code:
{code:java}
void SqlAtTimeZone(List<Object> list, ExprContext exprContext, Span s) :
{
List<Object> list2;
SqlOperator op;
}
{
{
checkNonQueryExpression(exprContext);
s.clear().add(this);
}
<AT> <TIME> <ZONE> { op = SqlAtTimeZone.INSTANCE; }
list2 = Expression2(ExprContext.ACCEPT_SUB_QUERY) {
list.add(new SqlParserUtil.ToTreeListItem(op, s.pos()));
list.addAll(list2);
}
} {code}
Below are snippets from the Postgres links to make it easier to refer to:
----
When a {{timestamp with time zone}} value is output, it is always converted
from UTC to the current {{timezone}} zone, and displayed as local time in that
zone. To see the time in another time zone, either change {{timezone}} or use
the {{AT TIME ZONE}} construct (see [Section
9.9.4|https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT]).
Conversions between {{timestamp without time zone}} and {{timestamp with time
zone}} normally assume that the {{timestamp without time zone}} value should be
taken or given as {{timezone}} local time. A different time zone can be
specified for the conversion using {{{}AT TIME ZONE{}}}.
*AT TIME ZONE (function definition)*
|{{timestamp without time zone}} {{AT TIME ZONE}} _{{zone}}_ → {{timestamp with
time zone}}
Converts given time stamp _without_ time zone to time stamp _with_ time zone,
assuming the given value is in the named time zone.
{{timestamp '2001-02-16 20:38:40' at time zone 'America/Denver'}} →
{{2001-02-17 03:38:40+00}}|
|{{timestamp with time zone}} {{AT TIME ZONE}} _{{zone}}_ → {{timestamp without
time zone}}
Converts given time stamp _with_ time zone to time stamp _without_ time zone,
as the time would appear in that zone.
{{timestamp with time zone '2001-02-16 20:38:40-05' at time zone
'America/Denver'}} → {{2001-02-16 18:38:40}}|
*Examples:*
{code:java}
SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'America/Denver';
Result: 2001-02-16 19:38:40-08
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE
'America/Denver';
Result: 2001-02-16 18:38:40
SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'Asia/Tokyo' AT TIME ZONE
'America/Chicago';
Result: 2001-02-16 05:38:40{code}
Here are examples from PrestoDB: - I think the only difference is that they use
"UTC" in their initial time stamp instead of offset, which is what Postgres and
(I think) SQL92 standardize on ("The SQL standard differentiates {{timestamp
without time zone}} and {{timestamp with time zone}} literals by the presence
of a “+” or “-” symbol and time zone offset after the time."):
{code:java}
SELECT timestamp '2012-10-31 01:00 UTC';
2012-10-31 01:00:00.000 UTC
SELECT timestamp '2012-10-31 01:00 UTC' AT TIME ZONE 'America/Los_Angeles';
2012-10-30 18:00:00.000 America/Los_Angeles {code}
> Support AT TIME ZONE
> --------------------
>
> Key: CALCITE-5301
> URL: https://issues.apache.org/jira/browse/CALCITE-5301
> Project: Calcite
> Issue Type: New Feature
> Components: core
> Reporter: Rong Rong
> Priority: Major
>
> Calcite already supports WITH TIME ZONE suffix in: CALCITE-3238
> a slightly different syntax supported by many is: {{AT TIME ZONE}}
> see:
> -
> https://www.postgresql.org/docs/14/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT
> -
> https://prestodb.io/docs/current/functions/datetime.html#time-zone-conversion
> would it be possible to support this syntax that converts a TIMESTAMP with
> time zone to another timezone?
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
