1

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?

Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323
Atihska
  • 129
  • 1
  • 6

1 Answers1

1

I was able to figure out as 

select *
from 
(
  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
Atihska
  • 129
  • 1
  • 6