failed defining the timestamp from csv file

Question

I am trying to store data into my Athena DB from S3 and I have date time format that looks like this :-

20171011133902

I am trying to set format to timestamp but it is not recognizable, thus no data was inserted into the table. I set the the format as bigint just to insert the data and my query looks something like this.

CREATE EXTERNAL TABLE IF NOT EXISTS default.elb_logs ( 'request_timestamp' bigint, 'id' int, .....)

I tried to convert in Quicksight, however when I edit data fields and change it to date, i became like this :-

2033-12-02T01:51:53.000Z

Can someone help me on how I can handle this type of date format?

score 2 · Answer 1 · answered Oct 17 '17 at 08:22

There are 2 solutions :-

I did used parseDate function on Quicksight and it worked. (without changing the datatype on Athena DB) -->only works when you are not using SPICE

parseDate(uploadtimestamp, 'yyyyMMddHHmmss')

I also figured out that I need to change it String (on Athena DB) in order for the conversion to date button to work correctly on QuickSight.

CREATE EXTERNAL TABLE IF NOT EXISTS default.elb_logs ( 'request_timestamp' string, 'id' int, .....)

score 0 · Accepted Answer · answered Oct 15 '17 at 20:23

I recommend loading your date time as a string, then parsing it to a timestamp in your select queries using the parse_datetime function. For JSON data like:

{"dt": "20171011133902", ... }

And your date/time field defined as a string:

CREATE EXTERNAL TABLE scratch.test_dates (
  `dt` string
)
...

A query using parse_datetime to reformat dt as a proper timestamp:

SELECT
  parse_datetime(dt, 'YYYYMMddHHmmss') as parsed_date
FROM 
  scratch."test_dates"

Will yield a timestamp with timezone (as UTC in your case):

2017-10-11 13:39:02.000 UTC

failed defining the timestamp from csv file

2 Answers2