Issue: BETWEEN does not work with datetime objects in SQlite
Author: LVerneyPEReN
State: closed
Created: 2021-08-25 14:47:27

Hi,

I have an issue where `BETWEEN` operation which does not work with a `DateTimeField` in SQLite when checking against Python `datetime.datetime` objects.

It seems this is due to the default adapter being used for such formats in SQLite is `str` conversion, which is not the same as an `isoformat`. Therefore, lexicographic comparison is broken afterwards.

See

```python
>>> import datetime
>>> str(datetime.datetime.now())
'2021-08-25 16:44:43.952660'
>>> datetime.datetime.now().isoformat()
'2021-08-25T16:44:56.216273'
```

And the extra "T" between date and time part.

Thanks!

Sqlite does not have a dedicated date/datetime type. It is up to you, the developer, to ensure that your datetimes are stored in a way that supports correct lexicographic ordering. Additionally, Peewee does not apply any conversion at all to datetime objects - it hands them as-is to the db driver. It is actually the sqlite driver that is handling the conversion for sqlite: (`import sqlite3; sqlite3.adapters`). This is consistent and supports lexicographic ordering, but if you've stored your datetimes in ISO format in Sqlite, then you may have some work to do to get them working properly with python's sqlite3 module.

You can:

* register a different adapter for `datetime.datetime` that converts them to string in iso format - this may be the easiest
* write a custom `DateTimeField` subclass that implements a `db_value()` method that returns the datetime in isoformat
* update your data to use the python format (probably undesirable, but mentioning it as a possibility)

All that said, at the end of the day this is not really a peewee issue -- this is a consequence of the behavior of the python sqlite driver and sqlite's own lack of a dedicated datetime type.

Thanks for the very quick response, this is indeed an issue due to having iso formatted values in db!

I tried to debug how they happened to get there, and it seems `DateTimeField` does not convert iso formatted datetime strings to a date object, but rather append them silently to the database.

This seems to be an easy mistake which could happen often, and quite silent.

What about extending https://github.com/coleifer/peewee/blob/master/peewee.py#L4971-L4975 to also support isoformat formats?

I think something like

```python
formats = [
        '%Y-%m-%dT%H:%M:%S.%f%z',
        '%Y-%m-%dT%H:%M:%S.%f',
        '%Y-%m-%d %H:%M:%S.%f',
        '%Y-%m-%dT%H:%M:%S%z',
        '%Y-%m-%dT%H:%M:%S',
        '%Y-%m-%d %H:%M:%S',
        '%Y-%m-%d',
]
```

should handle all these cases properly.

**EDIT**: The same formats addition is probably required as well in `DateField` and `TimeField`. I may have missed something, but I don't see any reason why isoformatted strings would not get promoted to relevant date objects for proper addition by peewee.

Thanks!

That will allow you to read them back but the problem is still that the data in your db was written in iso format.

Subclassing and implementing your formats and the corresponding db_value() method should cover all the bases.

To expand on my comment, there are 2 sides to the adaptation process. The side where you take a python value (a datetime in this example) and convert it to something the db understands, and the other side where you get a value from the db and convert it to something python understands.

Just extending the `formats` won't really help because it only affects the 2nd side (going from db -> python). That's why I suggested implementing `db_value()` which will correctly convert the datetime to an isoformat string before sending it to the db.

```python

# minimal example
class ISODateTimeField(DateTimeField):
    def db_value(self, val):
        if val is not None:
            return val.isoformat()
    def python_value(self, val):
        if val is not None:
            return datetime.datetime.strptime(val, '%Y-%m-%dT%H:%M:%S.%f')
```

Sorry, I think my latest comment may not have been clear about the details of my error (and history) and my proposal.

First, I should emphasize that BETWEEN operation is behaving as expected for datetime objects. The issue boils down to database insertion. Then, I should also emphasize that I do not have a pre-existent database but am creating a database from scratch using Peewee from the beginning and pushing data into it.

My mistake and the source of this issue boils down to how Peewee deals with incompatible values with compatible types for datetime fields.

Here is a minimal illustration about upon the quickstart page of the docs :


```python
from datetime import datetime
from peewee import *

db = SqliteDatabase('people.db')

class Person(Model):
    name = CharField()
    birthday = DateTimeField()

    class Meta:
        database = db # This model uses the "people.db" database.

db.connect()
db.create_tables([Person])


# Create a person with a datetime object as birthday, this is the most typical use case
uncle_bob = Person(name='Bob', birthday=datetime(1960, 1, 15, 1, 0, 0))
uncle_bob.save()

# Create a person with a string representing a date. String format matches one of https://github.com/coleifer/peewee/blob/master/peewee.py#L4971-L4975 so we hit https://github.com/coleifer/peewee/blob/master/peewee.py#L4948 and it is promoted to datetime object.
# This is therefore **the same behavior** as before, as expected
uncle_sam = Person(
    name='Sam',
    birthday="1960-01-15 01:00:00"
)
uncle_sam.save()

# Create a person with an isoformatted string. This format does not match any of the known formats for DateTimeField, so we hit https://github.com/coleifer/peewee/blob/master/peewee.py#L4949-L4951 which is completely silent and the value is inserted as is in the database.
# This is how I ended up with isoformatted dates in my db, by (incorrectly) manipulating an isoformatted string at some point (while I thought I had a datetime object) and Peewee / sqlite3 not throwing any error at me.
uncle_tom = Person(
    name='tom',
    birthday="1960-01-15T01:00:00Z"
)
uncle_tom.save()

# Note that when the object type is incompatible, sqlite3 will shout. But for any string which does not match a known date format, it gets inserted as is in the db.
uncle_guy = Person(name='Guy', birthday="This is definitely not a date")
uncle_guy.save() #
```



I feel like the fact that Peewee supports some date string formats is giving the wrong message that passing string instead of datetime object is safe, and one is likely to assume isoformatted datetime is fine. This is not the case.

So, I would like Peewee to minimize the possibility to reproduce this error (while trying to reduce the extra work on Peewee to a minimum) by:
* Adding new formats in https://github.com/coleifer/peewee/blob/master/peewee.py#L4971-L4975 to support isoformatted dates the same way as the other date format. See https://github.com/coleifer/peewee/issues/2459#issuecomment-905605324.
* Probably add a `logging.warning` item in https://github.com/coleifer/peewee/blob/master/peewee.py#L4950 so that this promotion error is no longer silent and the user has some form of easy discovery that they are not using Peewee the right way.

I can offer to handle a PR for this if this can be useful and you agree with my reading of this issue.

I'd suggest doing stronger validation before insertion into the DB and keeping in mind Sqlite's loose typing -- for fiddly things like datetimes this can be a gotcha for Sqlite users as it is up to you to be consistent about how you insert them to preserve ordering. I don't plan to make changes on the Peewee side at this time.