Peewee: Fractional DateTimeField values

[ParthGandhi]

The fractional part of a datetime object seems to be always truncated. Looking through the code a bit, I also see that it doesn't expect there to be one: https://github.com/coleifer/peewee/blob/master/peewee.py#L204

I assume this is primarily cause not all databases support fractional seconds (older versions of MySQL, for instance). Is there a way to optionally support it if the database underneath supports it?

[coleifer]

What database are you using? The code you've referenced is not relevant to your question as far as I understand what you're asking. The code you want is probably here:

https://github.com/coleifer/peewee/blob/1b021602a222485d5f28857a363cd378dc874df0/peewee.py#L1050-L1068

Basically, peewee will just pass the datetime object back to the database driver (psycopg2, sqlite3, pymysql, etc). From there, it's up to the database driver what to do with the Python datetime object. When Peewee reads the value _back_ from the database, some dbs (SQLite) may return it as a string, so there is logic to convert the string into a datetime object. As you can see in the formats list, peewee will attempt to parse out microseconds.

[ParthGandhi]

Yeah, you're right. Though I still can't get it to work, so maybe i'm missing something.

I'm using MySQL 5.7.

An example (mostly copied from the docs) is:

class Person(Model):
  name = CharField()
  birthday = DateTimeField()

  class Meta:
      database = db

p = Person(name='Bob', birthday=datetime.datetime.now())
p.save()

Person.get(Person.name == 'Bob').birthday # output: datetime.datetime(2015, 7, 21, 14, 57, 26)

But... looking at the SQL table directly shows that it isn't stored entirely (i.e. the value is stored as "2015-07-21 14:57:26"). So its a problem during storage, not retrieval. Note that the default fractional part for a DATETIME column in MySQL (>= 5.6.4) is 0 (https://dev.mysql.com/doc/refman/5.7/en/fractional-seconds.html).

[coleifer]

I'd suggest checking out your python driver, as Peewee really should just be passing back the datetime object directly to it.

[ParthGandhi]

Aren't the drivers a part of peewee? Unless I misunderstand what you mean.

[coleifer]

No, pymysql and mysql-python, etc are separate packages.

[ParthGandhi]

D'oh. Thanks, i'll look into those.

[ParthGandhi]

FYI, the problem wasn't on PyMySQLs end.

What happens is that the default QueryCompiler (https://github.com/coleifer/peewee/blob/master/peewee.py#L1350) datetime conversion results in columns being created as "DATETIME". But MySQL defaults fractional parts to 0 so any saves resulted in the datetime being truncated. The solution is to override the field_map when creating the Database instance: db = MySQLDatabase(..., fields={'datetime': 'DATETIME(6)'}). This forces all DATETIME columns to have fsp 6.

[coleifer]

You can create a custom field type or specify one or more field_overrides when instantiating your database.

• http://docs.peewee-orm.com/en/latest/peewee/models.html#creating-a-custom-field
• http://docs.peewee-orm.com/en/latest/peewee/api.html#Database

[binderclip]

After read the information you provided, I got this, and it seems work:

database = MySQLDatabase(dbname, fields={'datetime_fsp6': 'DATETIME(6)'})

class DateTimeFsp6Field(DateTimeField):
    db_field = 'datetime_fsp6'

class FooModel(Model):
    ctime = DateTimeFsp6Field(default=datetime.datetime.now)

[coleifer]

Thanks for sharing your solution @binderclip!

[luisulloa]

Shouldn't @binderclip be the default for Mysql ?

[mindojo-victor]

@luisulloa IIUC, not all MySql versions support this