Peewee: SQLite datetime field is str after save

[lucasrc]

class Customer(BaseModel):

date_test = DateTimeField()

Customer(date_test=now()).save()
customer = Customer.get()
customer.date_test # is a str now not datetime obj

[coleifer]

I don't know what you're talking about:

In [1]: from peewee import *

In [2]: db = SqliteDatabase(':memory:')

In [3]: class Test(Model):
   ...:     date = DateTimeField()
   ...:     class Meta:
   ...:         database = db
   ...:         

In [4]: Test.create_table()

In [5]: t = Test(date=datetime.datetime.now())

In [6]: t.save()
Out[6]: 1

In [7]: Test.get().date
Out[7]: datetime.datetime(2018, 1, 19, 9, 5, 6, 364642)

[lucasrc]

Sorry I found the error: Its a timezone. In postgress works very well

import datetime

from peewee import SqliteDatabase, Model, DateTimeField

from pytz import timezone

eastern = timezone('US/Eastern')
db = SqliteDatabase(':memory:')

class Test(Model):

class Meta:
    database = db

date = DateTimeField()

Test.create_table()
Test(date=datetime.datetime.now(tz=eastern)).save()

test = Test.get()
assert isinstance(test.date, datetime.datetime)

Traceback (most recent call last):
File "test_peewee.py", line 24, in
assert isinstance(test.date, datetime.datetime)
AssertionError

[coleifer]

A-ha, thanks @lucasrc ... So SQLite does not have a native datetime column type (just text, blob, int, float and null). Because of this datetimes are stored in lexicographically-sortable strings (YYYY-mm-dd HH:MM:SS.ffffff by default). When Peewee pulls data out of the db, it attempts to convert the string into a datetime using a list of supported formats.

When a timezone is included, the format becomes YYYY-mm-dd HH:MM:SS.ffffff-ZH:ZM -- which is not among the supported formats. Failing to convert to datetime, peewee just returns the string.

To support timezones with SQLite, you might (untested, beware):

class DateTimeTZField(DateTimeField):
    def python_value(self, value):
        if value is None: return
        datetime_str, zone = value.rsplit(' ', 1)  # Expected YYYY-mm-dd HH:MM:SS.ffffff ZZZZ
        val = datetime.datetime.strptime(datetime_str, '%Y-%m-%d %H:%M:%S.%f')
        if zone.startswith('-'):
            mult = -1
            zone = zone[1:]
        else:
            mult = 1
        zh, zm = int(zone[:2]), int(zone[2:])
        offset = FixedOffset(mult * (zh * 60 + zm))
        return val.replace(tzinfo=offset)

    def db_value(self, value):
        return value.strftime('%Y-%m-%d %H:%M:%S.%f %z') if value else None

I think in 3.2 you can use %z with strptime, but since Peewee supports 2.7 I don't think that behavior can be relied upon.

[coleifer]

As I was thinking -- you know, using timezones will break lexicographic sorting. You're best off converting everything to UTC and storing it as a naive datetime.

[lucasrc]

Thanks man for explanaition