I am working with Presto on Amazon Athena. I have a string time column in a table and now I want to change that time to date time type and then query data for selected dates.

Is there a direct way to do so? One way I could think of is

1) add a new column 
2) insert values into it with converted date 
3) Query using the new column

Here I am stuck with the 2nd step with INSERT so need help with that

  ALTER TABLE "db"."table" 
    ADD COLUMN new_date timestamp

-- Need correction in select statement that I don't understand
    INSERT INTO "db"."table" (new_date)
    (SELECT new_date from_iso8601_date(substr(timestamp,1,10))

Could some one help me with correction and if possible a better way of doing it?

  • FYI: Presto SQL is now Trino Jan 29 '21 at 21:19

I was able to figure out as

select *
  SELECT from_iso8601_date(substr(timestamp,1,10)) as day,*
  FROM "nds"."unacast_sample_august_2018"
WHERE  day > date_parse('2018-08-26', '%Y-%m-%d')  
 limit 10
  • 1
    @ErwinBrandstetter Thanks for pointing out. Sorry, this is a presto query working on aws Athena. My bad, I have edited the question.
    – Atihska
    Sep 20 '18 at 22:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.