[Sqlalchemy-tickets] Issue #3257: DATE/DATETIME/TIME names on SQLite use NUMERIC affinity (zzzeek/sqlalchemy)

[Mike B. <iss...@bi...>]

New issue 3257: DATE/DATETIME/TIME names on SQLite use NUMERIC affinity
https://bitbucket.org/zzzeek/sqlalchemy/issue/3257/date-datetime-time-names-on-sqlite-use

Mike Bayer:

this NUMERIC affinity is bypassed when our ISO date format puts non-numerics in, but if the format is changed to be all digits, it fails.  We can consider changing the DDL names on SQLite to DATE_CHAR, DATETIME_CHAR, TIME_CHAR or similar.   would need to receive both names within a reflection context:

```
#!python


from sqlalchemy.dialects.sqlite import DATE, DATETIME, TIME
from sqlalchemy.ext.compiler import compiles

# fix by using CHAR_ (or TEXT, whatever)
#@compiles(DATE, 'sqlite')
#@compiles(DATETIME, 'sqlite')
#@compiles(TIME, 'sqlite')
def compile_date(element, compiler, **kw):
    return "CHAR_%s" % element.__class__.__name__

from sqlalchemy import Table, Column, create_engine, MetaData, select
import datetime


m = MetaData()
t = Table(
    'dates', m,
    Column('date', DATE(
        storage_format="%(year)04d%(month)02d%(day)02d",
        regexp=r"(\d{4})(\d{2})(\d{2})",
    )),
    Column('datetime', DATETIME(
        storage_format="%(year)04d%(month)02d%(day)02d%(hour)02d%(minute)02d%(second)02d",
        regexp=r"(\d{4})(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})",
    )),
    Column('time', TIME(
        storage_format="%(hour)02d%(minute)02d%(second)02d",
        regexp=r"(\d{2})(\d{2})(\d{2})",
    ))
)

e = create_engine("sqlite://", echo='debug')
m.create_all(e)

now = datetime.datetime.today().replace(microsecond=0)
nowdate = now.date()
nowtime = now.time()

with e.begin() as conn:
    conn.execute(
        t.insert().values(
            date=nowdate,
            datetime=now,
            time=nowtime
        )
    )
    row = conn.execute(select([t.c.date, t.c.datetime, t.c.time])).first()
    assert row == (nowdate, now, nowtime), "%s %s" % (row, (nowdate, now, nowtime))

```


Responsible: sqlalchemy_sprinters