Converting to Timestamp With Time Zone Failed on Athena

converting to timestamp with time zone failed on Athena

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

Convert string with time zone offset to datetime in Athena

If all dates are exactly in this format you can replace space with T and use from_iso8601_timestamp, which will return timestamp with time zone and then you can handle timezone either using AT TIME ZONE 'UTC' or just custing to timezone:

select cast(from_iso8601_timestamp(replace('2021-08-17 06:55:22.819-0400', ' ', 'T')) AS timestamp) 

Output:













_col0
2021-08-17 10:55:22.819

Convert DateString with offset to a valid Date/Timestamp in Athena / Presto

Use from_iso8601_timestamp:

presto> select from_iso8601_timestamp('2016-09-21T10:01:43-04:00');
_col0
--------------------------------
2016-09-21 10:01:43.000 -04:00

(tested on Presto 309, but should work in Athena too)

Results fetched from AWS Athena have different values in timestamp column

The time change is most likely due to the SQL client adjusting the time for your timezone.

You can test this with:

SELECT
the_field,
CAST(the_field AS VARCHAR)
FROM table

The second column will convert the timestamp into a string before it reaches your SQL client, so no timezone conversion will be applied. If the two columns are different, it is an indication that the time shift is being caused outside of Amazon Athena, inside your SQL client.



Related Topics



Leave a reply



Submit