CODEWITHSUNDEEP
hivehiveqlprestoamazon-athena
Blu3
Blu3

Reputation: 676

Amazon Athena time date as string column

Athena partition are by year/month/date and is being imported as STRING column by GLUE.So day is a type string. My requirement is extract the day from the current time stamp and compare to my day column/partition. 

SELECT *
FROM "db1"."tbl1"
WHERE year = cast(extract(year from (CURRENT_DATE - interval '7' day)) as varchar) 
AND month = lpad(cast(extract(month from (CURRENT_DATE - interval '7' day)) as varchar),2,'0') 
AND day = lpad(cast(extract(day from (CURRENT_DATE - interval '7' day)) as varchar),2,'0') 
limit 10

This extracts single day . I want all days between now and past 7 days.

Upvotes: 5

Views: 27480

Answers (2)

Blu3
Blu3

Reputation: 676

Working through presto docs I figured date_parse was a easier approach. Here are the steps SELECT current_date CONCAT concat(year, month, day) and then date_parse

cast(date_parse(concat(year, month, day), '%Y%m%d') as date) BETWEEN current_date - interval '7' day AND current_date

[https://prestodb.io/docs/current/functions/datetime.html][1]

Upvotes: 5

daronjp
daronjp

Reputation: 694

I approached this by creating a sequence of the last seven days and extracting the year, month, and day from those values. The year/month/day columns in my Athena data were stored as integers, so I didn't need to cast the results of the EXTRACT function, but I cast the results in this answer to match your use case.

-- there may be a more elegant approach, but this worked for me
-- create a CTE containing the last seven dates
WITH dates AS (
  SELECT
    date_add('day', n, current_date) AS date
  FROM (
    SELECT
      ROW_NUMBER() OVER ()-7 AS n
    FROM db1.tbl1
    LIMIT 7
  )
),

-- extract the year, month, and day for joining
date_parts AS (
  SELECT
    CAST(EXTRACT(YEAR FROM date) AS VARCHAR) AS year,
    CAST(EXTRACT(MONTH FROM date) AS VARCHAR) AS month,
    CAST(EXTRACT(DAY FROM date) AS VARCHAR) AS day
  FROM dates
)

-- return all results from the last seven days
SELECT 
  * 
FROM date_parts AS dp
  JOIN db1.tbl1 AS t1 ON dp.year = t1.year AND dp.month = t1.month AND dp.day = t1.day;

Upvotes: 3

Related Questions