Date(time) parameters lose type in CTAS

[EdwardJRoss]

Consider the query:

import datetime
import pyathena
cursor = pyathena.connect().cursor()
cursor.execute('CREATE TABLE test AS SELECT %(date)s as date', {'date': datetime.date(2018,1,1)})
cursor.execute('SELECT * FROM test')
result = cursor.fetchone()

Expected output:

result == (datetime.date(2018,1,1),)

Actual output:

result == '2018-01-01'

Explanation:
In formatter.py we have

def _format_date(formatter, escaper, val):
if escaper is _escape_presto:
    return "date'{0}'".format(val.strftime("%Y-%m-%d"))
else:
    return "'{0}'".format(val.strftime("%Y-%m-%d"))

and the escaper is set in DefaultParameterFormatter:

    if operation.upper().startswith('SELECT') or operation.upper().startswith('WITH'):
        escaper = _escape_presto
    else:
        escaper = _escape_hive

So in this case because our operation starts with CREATE it does an _escape_hive and removes the date format. (Similary for datetime)

Is there a reason this case needs to be treated differently?

[laughingman7743]

It may not be necessary to separate the date conversion process between Hive and Presto.
It may be good to convert everything with the cast function. 🤔