How to store datetime with millisecond precision in SQL database

Question

With a float value representing date and time with millisecond precision:

import datetime
float_time = 1485538757.29289
print datetime.datetime.fromtimestamp(float_time) 

prints:

2017-01-27 09:39:17.292890

To store it in db:

from sqlalchemy import Column, DateTime
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class MyTable(Base):
    __tablename__ = 'mytable'
    time_created = Column(DateTime, nullable=False)

But saved value is rounded down to 2017-01-27 09:39:17 (from 2017-01-27 09:39:17.292890). Is there is a solution?

Answer 1

It depends on the SQL database you're using. They differ in precision:

PostgreSQL: Default 1 microsecond. (See docs for far-future/past caveats.)

MySQL: Default 1 second. Millisecond / microsecond precision optional after version 5.6.4.

MariaDB: Default 1 second. Millisecond / microsecond precision optional since version 5.3.

Transact-SQL (Microsoft SQL): Rounded to increments of .000, .003, or .007 seconds

SQLite: Datetimes can be stored as strings with arbitrary precision, but "only the first three digits are significant".

Oracle: Default 1 microsecond. Optional precision to 1 nanosecond.

Note:

• Millisecond: 0.001s
• Microsecond: 0.000001s
• Nanosecond: 0.000000001s