Presto SQL:使用查询结果出现的时区字符串更改时区不起作用 [英] Presto SQL : Changing time zones using time zone string coming as a result of a query is not working

问题描述

我通过 Mode Analytics Platform 连接到 AWS Athena 并使用其查询引擎(基于 Presto 0.172)查询表.这个表 public.zones 将时区信息存储在我感兴趣的一些区域的名为 time_zone 的列中，存储为 varchar.

例如，如果我输入:

SELECT time_zone从公共区域限制 4；

我得到(如预期):

time_zone----------美国/太平洋美国/东部美国/东部美国/东部

我可以运行这个测试查询:

SELECT时间戳 '2017-06-01 12:34:56.789' AT TIME ZONE 'US/Eastern' AS time_eastern，时区从公共区域限制 4；

我得到(如预期)

time_eastern time_zone---------------------------------- ----------2017-06-01 08:34:56.789 美国/美国东部/太平洋2017-06-01 08:34:56.789 美国/美国东部/美国东部2017-06-01 08:34:56.789 美国/美国东部/美国东部2017-06-01 08:34:56.789 美国/美国东部/美国东部

现在，我想在我从区域表中查询的不同时区中表示相同的时间字符串 '2017-06-01 12:34:56.789'.我希望运行以下查询.(它在 PostgreSQL 上运行).

SELECT时间戳 '2017-06-01 12:34:56.789' AT TIME ZONE time_zone AS time_custom，时区从公共区域限制 4；

我收到以下错误:

[Simba][AthenaJDBC](100071) AWS Athena 客户端抛出错误.第 2:52 行:输入TIME ZONE time_zone"没有可行的替代方案

这在 Presto SQL/AWS Athena 查询引擎中不起作用的原因是什么?

任何人都可以提出任何解决方法或我的语法错误(如果有的话)是什么?

解决方案

AT TIME ZONE 只接受文字或间隔.

Presto 320 添加了 with_timezone (对于 timestamp 值)at_timezone(对于 timestamp with time zone 值)正是为此目的.

如果您使用的是较旧的 Presto 版本(例如撰写本文时的 Athena)，您可以使用以下解决方法.您可以将时间戳值转换为 varchar，与 zone 连接并转换为 timestamp with time zone.

presto>select cast(cast(t as varchar) || ' ' || zone as timestamp with time zone)from (values (timestamp '2017-06-01 12:34:56.789', 'US/Pacific')) x(t, zone);_col0---------------------------------------------2017-06-01 12:34:56.789 美洲/洛杉矶(1 行)

(注意:在 Presto 320 上测试过.如果这还不能在 Athena 上运行，请告诉我.)

I am connecting to AWS Athena through Mode Analytics Platform and querying a table using its Query Engine ( which is based on Presto 0.172 ). This table public.zones has time zone information stored in a column called time_zone on some regions I am interested in, stored as varchar.

For example if I type:

SELECT time_zone 
FROM public.zones
LIMIT 4;

I get (as expected):

time_zone
----------  
US/Pacific 
US/Eastern 
US/Eastern 
US/Eastern 

I can run this test query:

SELECT 
  timestamp '2017-06-01 12:34:56.789' AT TIME ZONE 'US/Eastern' AS time_eastern,
  time_zone 
FROM public.zones
LIMIT 4;

and I get (as expected)

time_eastern                        time_zone
----------------------------------  ----------
2017-06-01 08:34:56.789 US/Eastern  US/Pacific
2017-06-01 08:34:56.789 US/Eastern  US/Eastern
2017-06-01 08:34:56.789 US/Eastern  US/Eastern
2017-06-01 08:34:56.789 US/Eastern  US/Eastern

Now, I want to represent the same time string '2017-06-01 12:34:56.789' in different time zones that I query from the zones table. I expected the following query to run. (It runs on PostgreSQL).

SELECT 
  timestamp '2017-06-01 12:34:56.789' AT TIME ZONE time_zone AS time_custom,
  time_zone 
FROM public.zones
LIMIT 4;

I get the following error:

[Simba][AthenaJDBC](100071) An error has been thrown from the AWS Athena client. 
line 2:52: no viable alternative at input 'TIME ZONE time_zone'

What is the reason for this not working in Presto SQL / AWS Athena Query Engine ?

Can anyone suggest any work-arounds or what is my syntactical error if any?

解决方案

AT TIME ZONE accepts only literal or interval.

Presto 320 adds with_timezone (for timestamp values) at_timezone (for timestamp with time zone values) exactly for this purpose.

If you are using older Presto version (such as Athena as of this writing), you can use following workaround. You can cast your timestamp value to a varchar, concatenate with zone and cast to timestamp with time zone.

presto> select cast(cast(t as varchar) || ' ' || zone as timestamp with time zone)
  from (values (timestamp '2017-06-01 12:34:56.789', 'US/Pacific')) x(t, zone);
                    _col0
---------------------------------------------
 2017-06-01 12:34:56.789 America/Los_Angeles
(1 row)

(Note: tested on Presto 320. If this doesn't work on Athena yet, let me know.)

这篇关于Presto SQL:使用查询结果出现的时区字符串更改时区不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！