Comparison query on nested date partition in Presto/Athena

Question

I have parquet data stored on S3, partitioned in the format that Hive understands

s3://<base_path>/year=2019/month=11/day=08/files.pq

The table schema also specifies year, month, day as partition fields.

Is it possible to comparison queries, specifically LIKE, IN, BETWEEN on dates, with this organization of data? An AWS Athena best practices blog seems to suggest its possible (SELECT count(*) FROM lineitem WHERE l_shipdate >= '1996-09-01' AND l_shipdate < '1996-10-01'), but I could not figure out how to specify the composite field (l_shipdate in query), either during table creation or during query.

Answer 1

Yes, it is possible, but it doesn’t look very elegant)

SELECT col1, col2
FROM my_table 
WHERE CAST(date_parse(concat(CAST(year AS VARCHAR(4)),'-',
                             CAST(month AS VARCHAR(2)),'-',
                             CAST(day AS VARCHAR(2))
                             ), '%Y-%m-%d') as DATE) 
BETWEEN DATE '2018-01-01' AND DATE '2018-01-31'