add some kind of "microseconds optional" system to SQlite DATETIME
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
Comments (11)
-
reporter -
repo owner 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.
-
reporter 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()}}}?
-
repo owner 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
-
reporter That's awesome. Thanks!
-
repo owner - marked as enhancement
- changed title to add some kind of "microseconds optional" system to SQlite DATETIME
- changed component to sqlite
- assigned issue to
- changed milestone to 0.8.0
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.
-
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 :)
-
repo owner 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 !
-
repo owner packaged up in 0.8 as a pull + 59ce77ca93bc3a0af054fbead17e927172047165 with the final CHANGES update and some spelling errors.
-
repo owner - changed status to resolved
-
repo owner - removed milestone
Removing milestone: 0.8.0b1 (automated comment)
- Log in to comment
here's a unit test: (link: https://bitbucket.org/wilsaj/sqlalchemy/src/5fb332746f18/test/dialect/test_sqlite.py#cl-75)