Can you use a column for the timezone parameter of AT TIME ZONE in Presto / Athena?
It indeed looks like a bug in the engine. What is interesting however is, that the underlying function works with a column parameter. So you can use this as a workaround.
SELECT at_timezone(date_utc,x.timezone)
FROM
(
SELECT
TIMESTAMP '2018-09-09 12:00:00' as date_utc,
'America/Chicago' as timezone
) x;
Presto SQL : Changing time zones using time zone string coming as a result of a query is not working
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: Publish a table with timezone altered to the correct zone
SELECT CAST(SUBSTR(CAST((FROM_UNIXTIME(CAST(1578514469000 AS BIGINT)/1000) AT TIME ZONE 'US/Pacific') AS varchar), 1, 23) AS timestamp)
converting to timestamp with time zone failed on Athena
Short summary:
SELECT CAST(From_iso8601_timestamp('2018-01-01T15:00:00Z') AS timestamp) as "real_date"
Full story:
Unfortunately Athena doesn't fully support all Presto features, it has limitations and is technically a few versions behind Presto. There's some attempt to make Athena integrate closely with the AWS Glue Metastore, which while based on Hive's metastore has some inconsistencies. I wish that Spark, Hive, Glue, Athena, Presto et al would just work with the same metastore, it would make life easier, but back to your issue:
This document about an older teradata fork of Presto mentions some issues with timestamp in presto:
Presto’s method for declaring timestamps with/with out timezone is not
sql standard. In Presto, both are declared using the word TIMESTAMP,
e.g. TIMESTAMP '2003-12-10 10:32:02.1212' or TIMESTAMP '2003-12-10
10:32:02.1212 UTC'. The timestamp is determined to be with or without
timezone depending on whether you include a time zone at the end of
the timestamp. In other systems, timestamps are explicitly declared as
TIMESTAMP WITH TIME ZONE or TIMESTAMP WITHOUT TIME ZONE
The version of Presto that Athena is forked from does support both timestamp
and timestamp with timezone
but with that quirk as mentioned in the teradata docs which shouldn't be an issue. The real issue is that Athena does not support timestamp with timezone.
The presto docs you've linked to show that the function returns a value of that unsupported type timestamp with timezone
, so you need to cast it as something else that is supported. It's an oversight that Athena allows functions and casting to a datatype that is then not supported, and hopefully that will be remedied, but for now you have to work around it.
What you need to do is use the CAST()
function around that function call, which will change the type from timestamp with time zone
into timestamp
Unfortunately you probably can't cast the string directly to a timestamp, although it depends on how the string is formatted. You also can't use the style of casting where you write timestamp
before the string e.g. can't do timestamp '2018-01-01 15:00:00'
for reasons I will explain below.
Type returned by the from_iso1601_timestamp()
function
SELECT typeof("real_date") AS real_date_type
FROM
(
SELECT From_iso8601_timestamp('2018-01-01T15:00:00Z') as "real_date"
)
timestamp with time zone
This doesn't work
SELECT typeof("real_date") AS real_date_type
FROM
(
SELECT CAST('2018-01-01T15:00:00Z' AS timestamp) as "real_date"
)
SQL Error [FAILED]: INVALID_CAST_ARGUMENT: Value cannot be cast to
timestamp
This style of Casting also returns timestamp with timezone :(
Note that the SELECT part of this works, and it says that it is a timestamp
, but for some internal inconsistency reason you can't create a view and you'll get an error.
CREATE OR replace VIEW test
AS
SELECT typeof( "real_date" ) AS real_date_type
FROM
(
SELECT timestamp '2018-01-01 15:00:00' as "real_date"
)
SQL Error [FAILED]: Could not initialize class
com.facebook.presto.util.DateTimeZoneIndex
For whatever reason, creating a view requires that java class while parsing the value in the select doesn't. It's a bug that should be addressed.
This works yay
CREATE OR REPLACE VIEW test
AS
SELECT typeof("real_date") AS real_date_type
FROM
(
SELECT CAST(From_iso8601_timestamp('2018-01-01T15:00:00Z') AS timestamp) as "real_date"
)
How do I convert time zones in AWS Athena
Ahtena supports at_timezone
function:
WITH dataset (t, tz) AS (
VALUES (TIMESTAMP '2001-08-22 03:04:05.321 America/Los_Angeles', 'America/New_York')
)
select at_timezone(t, tz)
FROM dataset
Output:
_col0 |
---|
2001-08-22 06:04:05.321 America/New_York |
Related Topics
Possible to Restore a Backup of SQL Server 2014 on SQL Server 2012
How to Convert an Int to a Zero Padded String in T-Sql
Using Table Variable with Sp_Executesql
How to Perform a Select Query in a Do Block
The Argument 1 of the Xml Data Type Method "Value" Must Be a String Literal
Ignore Certain Columns When Using Bulk Insert
Ms SQL Server Pivot Table with Subquery in Column Clause
Is There a Performance Difference Between Between and in with MySQL or in SQL in General
Why Do I Need to Explicitly Specify All Columns in a SQL "Group By" Clause - Why Not "Group by *"
Merging Date Intervals in SQL Server
Calculate Time Difference (Only Working Hours) in Minutes Between Two Dates
Postgres Trigger After Insert Accessing New
How to Get Max(Date) from Given Set of Data Grouped by Some Fields Using Pyspark
Postgres: Upgrade a User to Be a Superuser
Selecting Specific Row Number in SQL
SQL Query to Translate a List of Numbers Matched Against Several Ranges, to a List of Values