Retrieve iso 8601 timestamp

Question

How to fetch timestamp values which are in the iso 8601 format from Athena to QuickSight.

| id | shipdate   | status| iso_8601 |   Epoc   |
| --- | --- | --- | --- | --- |
| 1 | 2022-02-09 07:00:00  |           OPEN |         2022-07-26T00:15:41+00:00 |   1658794541394 |
| 2 | 2022-08-02 20:00:00 |           CLOSED |       2022-07-31T21:46:23+00:00 |  1659303983100 |
| 3 |  2022-08-02 21:00:00 |           OPEN |          2022-07-31T21:46:23+00:01 | 1659303983100 |

I am using the below query,but i am unable to fetch any rows matching with the iso_8601 timestamp.

Query:

select epoc,iso_8601,shipdate,from_unixtime(cast(substr(cast(1658794541394 as varchar), 1, 10) AS bigint))
from newtable where  iso_8601 >= from_unixtime(cast(substr(cast(1658794541394 as varchar), 1, 10) AS bigint))

The from_unixtime(cast(substr(cast(1658794541394 as varchar), 1, 10) AS bigint)) is returning the value as 
2022-07-26 00:15:41.000 but the column iso_8601 has value 2022-07-26T00:15:41+00:00. Hence my query is not meeting the condition.

Could you please let me know how to resolve the issue.

Ashish_P · Answer

iso_8601 is interpreted as string , you need to convert with parseDate function i.e. `parseDate(replace(substring({date_in_ISO},1,19),'T'," "),'yyyy-MM-dd HH:mm:ss')`

For more help refer [parseDate](https://docs.aws.amazon.com/quicksight/latest/user/parseDate-function.html)

id	shipdate	status	iso_8601	Epoc
1	2022-02-09 07:00:00	OPEN	2022-07-26T00:15:41+00:00	1658794541394
2	2022-08-02 20:00:00	CLOSED	2022-07-31T21:46:23+00:00	1659303983100
3	2022-08-02 21:00:00	OPEN	2022-07-31T21:46:23+00:01	1659303983100

Retrieve iso 8601 timestamp

Relevant content