CODEWITHSUNDEEP
amazon-athenaapache-superset
Benjamin Kenner
Benjamin Kenner

Reputation: 21

How to convert a VARCHAR column to a Timestamp in a table

I am trying to convert a VARCHAR column from a table into a TIMESTAMP.

Currently my column looks like this:

2020-05-31T05:00:21Z

My goal is to transform this column in to a timestamp so I can use it for a visualization (Big Number with Trendline)

When I leave the column as it is, I get this error:

awsathena error: INVALID_CAST_ARGUMENT: Value cannot be cast to timestamp: 2020-07-07T18:56:56Z

When I change the type of the column to TIMESTAMP, I get this other error:

awsathena error: SYNTAX_ERROR: line 4:19: '>=' cannot be applied to varchar, timestamp with time zone

This is the query from the visualization:

SELECT date_trunc('day', CAST("eventtime" AS TIMESTAMP)) AS "__timestamp",
       COUNT(*) AS "count"
FROM "cloudtrail_logs_cloud_trail_elk"
WHERE "eventtime" >= '2020-07-01 00:00:00.000000'
  AND "eventtime" < '2020-07-08 00:00:00.000000'
GROUP BY date_trunc('day', CAST("eventtime" AS TIMESTAMP))
ORDER BY "count" DESC
LIMIT 50000;

Is there any way of changing the type of this column so I can use it for my visualization?

Upvotes: 1

Views: 2086

Answers (2)

Eduardo Y&#225;&#241;ez Parareda
Eduardo Y&#225;&#241;ez Parareda

Reputation: 10346

A possibility is to do the casting on the datetimes values:

SELECT date_trunc('day', CAST("eventtime" AS TIMESTAMP)) AS "__timestamp",
       COUNT(*) AS "count"
FROM "cloudtrail_logs_cloud_trail_elk"
WHERE "eventtime" >= TIMESTAMP '2020-07-01 00:00:00'
  AND "eventtime" < TIMESTAMP '2020-07-08 00:00:00'
GROUP BY date_trunc('day', CAST("eventtime" AS TIMESTAMP))
ORDER BY "count" DESC
LIMIT 50000;

or using between instead of >= and <

SELECT date_trunc('day', CAST("eventtime" AS TIMESTAMP)) AS "__timestamp",
       COUNT(*) AS "count"
FROM "cloudtrail_logs_cloud_trail_elk"
WHERE "eventtime" between TIMESTAMP '2020-07-01 00:00:00'
  AND TIMESTAMP '2020-07-08 00:00:00'
GROUP BY date_trunc('day', CAST("eventtime" AS TIMESTAMP))
ORDER BY "count" DESC
LIMIT 50000;

Upvotes: 0

Benjamin Kenner
Benjamin Kenner

Reputation: 21

In case someone runs in to the same Problem as i did, you can use this function:

from_iso8601_timestamp(eventtime)

in Superset here: Superser -> Edit Table -> Edit Column -> Expression

Superser-> Edit Table-> Expression

Upvotes: 1

Related Questions