add some kind of "microseconds optional" system to SQlite DATETIME

[wilsaj]

Seems like due to the way that datetimes are stored in sqlite (as strings), the comparisons are a little bit off when you compare to datetime objects where value.microseconds >= 0 because sqlite doesn't store microseconds if microseconds != 0

[wilsaj]

here's a unit test: (link: https://bitbucket.org/wilsaj/sqlalchemy/src/5fb332746f18/test/dialect/test_sqlite.py#cl-75)

class TestDateTime(fixtures.TestBase, AssertsExecutionResults):

    __only_on__ = 'sqlite'

    def test_datetime_comparison(self):
        meta = MetaData(testing.db)
        t = Table('datetime_table', meta, Column('id', Integer,
                  primary_key=True), Column('dt',
                  DateTime()))
        try:
            meta.create_all()
            testing.db.execute("INSERT INTO datetime_table (id, dt) "
                               "VALUES (1, '2011-12-31 19:20:15');")
            testing.db.execute("INSERT INTO datetime_table (id, dt) "
                               "VALUES (2, '2011-12-31 19:20:16');")
            testing.db.execute("INSERT INTO datetime_table (id, dt) "
                               "VALUES (3, '2011-12-31 19:20:17.000125');")
            eq_(t.select(t.c.dt).where(
                    t.c.dt == datetime.datetime(2011, 12, 31, 19, 20, 17, 125)
                    ).execute().fetchall(),
                [datetime.datetime(2011, 12, 31, 19, 20, 17, 125))]((3,))
            eq_(t.select(t.c.dt).where(
                    t.c.dt == datetime.datetime(2011, 12, 31, 19, 20, 16)
                    ).execute().fetchall(),
                [datetime.datetime(2011, 12, 31, 19, 20, 16))]((2,))
            eq_(t.select(t.c.dt).where(
                    t.c.dt >= datetime.datetime(2011, 12, 31, 19, 20, 16)
                    ).order_by(t.c.dt).execute().fetchall(),
                [datetime.datetime(2011, 12, 31, 19, 20, 16)),
                 (3, datetime.datetime(2011, 12, 31, 19, 20, 17, 125))]((2,))
        finally:
            meta.drop_all()

[Mike Bayer]

Replying to wilsaj:

Seems like due to the way that datetimes are stored in sqlite (as strings), the comparisons are a little bit off when you compare to datetime objects where value.microseconds >= 0 because sqlite doesn't store microseconds if microseconds != 0

I'm not really seeing a SQLAlchemy issue here - SQLite doesn't store dates in any particular way, and the test you have here is artificially inserting dates in a string format that is specifically A. not how SQLAlchemy does it and B. are impossible to match on microseconds. There's nothing I could see SQLAlchemy doing here as you just need to store the data in a format that is compatible with the lookup format you'll be using.

If you use SQLAlchemy's date formats, the microsecond is stored:

from sqlalchemy import *
import datetime

e = create_engine("sqlite://", echo='debug')
meta = MetaData(e)
t = Table('datetime_table', meta, 
        Column('id', Integer, primary_key=True), 
        Column('dt', DateTime())
    )

meta.create_all()

e.execute(
    t.insert(),
    [       dict(id=1, dt=datetime.datetime(2011, 12, 31, 19, 20, 15)),
        dict(id=2, dt=datetime.datetime(2011, 12, 31, 19, 20, 16)),
        dict(id=3, dt=datetime.datetime(2011, 12, 31, 19, 20, 17, 125))
    ](
)
)

def eq_(a, b):
    assert a == b, "%r != %r" % (a, b)

eq_(
    t.select(t.c.dt).where(
        t.c.dt == datetime.datetime(2011, 12, 31, 19, 20, 17, 125)
        ).execute().fetchall(),
    [datetime.datetime(2011, 12, 31, 19, 20, 17, 125))]((3,)
)

eq_(t.select(t.c.dt).where(
        t.c.dt == datetime.datetime(2011, 12, 31, 19, 20, 16)
        ).execute().fetchall(),
    [datetime.datetime(2011, 12, 31, 19, 20, 16))]((2,)
)

eq_(t.select(t.c.dt).where(
        t.c.dt >= datetime.datetime(2011, 12, 31, 19, 20, 16)
        ).order_by(t.c.dt).execute().fetchall(),
    [datetime.datetime(2011, 12, 31, 19, 20, 16)),
     (3, datetime.datetime(2011, 12, 31, 19, 20, 17, 125))]((2,)
)

Leaning towards a "workforme" here, though feel free to suggest what you had in mind.

[wilsaj]

Yep. This is more nuanced than I thought.

The behavior I was actually trying to get to is that comparisons against timestamps generated with {{{func.now()}}} are failing, because {{{func.now()}}} becomes {{{CURRENT_TIMESTAMP}}} and {{{CURRENT_TIMESTAMP}}} doesn't render microseconds so {{{func.now()}}} doesn't play nice with SQLAlchemy-generated datetime strings.

For example:

from sqlalchemy import Column, create_engine, Integer, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql.expression import func

Base = declarative_base()


class Foo(Base):
    __tablename__ = 'foo'

    id = Column(Integer, primary_key=True)
    last_updated = Column(DateTime, default=func.now())


engine = create_engine('sqlite:///foo.db')
db_session = sessionmaker(bind=engine)()
Base.metadata.create_all(bind=engine)

db_session.add(Foo())
db_session.commit()

The last_updated field will look something like '2012-01-03 16:00:00'

I hope that there is a cleaner way of doing it, but the best I've come up with for getting a current timestamp with microseconds is this:

SELECT strftime('%Y-%m-%d %H:%M:%f', 'now') || '00';

Could that replace {{{CURRENT_TIMESTAMP}}} as what gets rendered for {{{func.now()}}}?

[Mike Bayer]

sure:

from sqlalchemy.sql.functions import now

@compiles(now, 'sqlite')
def sl_now(element, compiler, **kw):
    return "strftime('%Y-%m-%d %H:%M:%f00', 'now')"

if you just don't want microseconds, make yourself a DateTime that doesn't have the microseconds:

from sqlalchemy.types import UserDefinedType

class MyDateTime(UserDefinedType):
    def get_col_spec(self):
        return "DATETIME"

    def bind_processor(self, dialect):
        def process(value):
            if value is not None:
                return value.strftime('%Y-%m-%d %H:%M:%f')
            else:
                return value
        return process
    def result_processor(self, dialect, coltype):
        def process(value):
            return datetime.datetime.strptime(value, '%Y-%m-%d %H:%M:%f')
        return process

[wilsaj]

That's awesome. Thanks!

[Mike Bayer]

it should be easier to do this though without creating a custom type. not sure why we have as of yet not added a "microseconds" flag to SQLite's type.

[natedub]

I've just attached a patch that adds a "truncate_microseconds" argument to sqlite's DATETIME and TIME. In order to implement this, I changed storage_format to use named parameters. For symmetry, the regexp now also supports named groupings, but positional groupings are still supported because it was easy to support both and I didn't want to have to update every regex in the code. The storage_format and regexp stuff was completely untested before and this also fixes that.

The changeset is also on my bitbucket: https://bitbucket.org/natedub/sqlalchemy

Hopefully that should do it, but let me know if there are any improvements I can make. Happy to help :)

[Mike Bayer]

this patch looks great ! I'll give it a run when I start building up the 0.8 tree. I like the tests and the has_named_groups check a lot, thanks !

[Mike Bayer]

packaged up in 0.8 as a pull + 59ce77ca93bc3a0af054fbead17e927172047165 with the final CHANGES update and some spelling errors.

[Mike Bayer]

Removing milestone: 0.8.0b1 (automated comment)