Issue: Fractional DateTimeField values
Author: ParthGandhi
State: closed
Created: 2015-07-21 21:29:06

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?


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.


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).


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


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


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


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


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.


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


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)
```


Thanks for sharing your solution @binderclip!


Shouldn't @binderclip be the default for Mysql ?

@luisulloa IIUC, not all MySql versions support this