[sqlalchemy] Re: Filter by year in datetime column
Would create_date = '2007-01-01' and create_date '2008-01-01' be acceptable? If so, something like this should work from sqlalchemy import and_ from datetime import date data = Table.query().filter(and_([Mikropost.c.create_date = date(2007, 1, 1), Mikropost.c.create_date date(2008, 1, 1)])) Hope that helps, Simon -Original Message- From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: 18 January 2008 13:22 To: sqlalchemy Subject: [sqlalchemy] Filter by year in datetime column Hello, pleas, i have begginer problem and question: In table (database is sqlite) is colum for create date (create_date = Field(DateTime, default = datetime.now)) I need query from table, with all item where have year of create date 2007. Is this the right way ? (this don`t work) data = Table.query().filter(func.year(Mikropost.c.create_date) == 2008) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Filter by year in datetime column
--On 18. Januar 2008 12:08:46 -0500 Rick Morrison [EMAIL PROTECTED]
wrote:
There are no generic date functions in SQLAlchemy (although work has begun
on them). So for now, you'll need to use date functions native to your
database.
For sqlite something like,
func.strftime('%Y', Mikropost.c.create_date) == '2008'
should work -- you may need to add additional percent signs depending on
how badly the '%Y' literal is mangled during parameter collection.
Such operations will likely trigger a full table scan. Depending on the
size of your data you might adjust the data model to the need of your query
and/or think about functional indexes (in case your database provides this
functionality).
Andreas
pgpQvohGvKt7k.pgp
Description: PGP signature
[sqlalchemy] Re: Filter by year in datetime column
There are no generic date functions in SQLAlchemy (although work has begun
on them). So for now, you'll need to use date functions native to your
database.
For sqlite something like,
func.strftime('%Y', Mikropost.c.create_date) == '2008'
should work -- you may need to add additional percent signs depending on how
badly the '%Y' literal is mangled during parameter collection.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Filter by year in datetime column
Such operations will likely trigger a full table scan SQLite dates are stored as strings anyway, AFAIK there is little one can do to avoid table-scans in SQLite based solely on date criteria. I use julian dates stored as integers when working with large datasets in SQLite, and convert as needed. Be interested to hear what others do about this. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Filter by year in datetime column
Rick Morrison wrote: Such operations will likely trigger a full table scan SQLite dates are stored as strings anyway, AFAIK there is little one can do to avoid table-scans in SQLite based solely on date criteria. I use julian dates stored as integers when working with large datasets in SQLite, and convert as needed. Be interested to hear what others do about this. well one can decompose dates into day-month-year triple of ints, but this definitely will need some wrapper to be made easy-to-use (and may need composite keys for example). --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
