Comparison query on nested date partition in Presto/Athena

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.

1 answer

  • answered 2019-11-08 10:39 Ilya Kisil

    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'