6

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?

alphanumeric
  • 17,967
  • 64
  • 244
  • 392
  • Save your full time stamp with floating value and display in intended format. Format the DateTIme at the time of manipulation of that data. Why dont you store full value on DB ????? – BetaDev Jan 27 '17 at 18:22
  • I do store this value as string in another Column. I just would like to make sure there is no other way to make it work with the Column of DateTime type. – alphanumeric Jan 27 '17 at 18:42
  • You can store this `00:00:00 through 23:59:59.997` in SQL datetime date type. Notice the milliseconds – BetaDev Jan 27 '17 at 18:50
  • Possible duplicate of [SqlAlchemy mysql millisecond or microsecond precision](http://stackoverflow.com/questions/29711102/sqlalchemy-mysql-millisecond-or-microsecond-precision) – Stephen Rauch Jan 27 '17 at 19:48
  • Which database are you using? – univerio Jan 27 '17 at 22:36
  • I am using mariaDb – alphanumeric Jan 27 '17 at 23:10

3 Answers3

7

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
meshy
  • 8,470
  • 9
  • 51
  • 73
  • The text I'm looking at says "MariaDB can also store microseconds with a precision between 0 and 6. If no microsecond precision is specified, then 0 is used by default." Perhaps I'm misinterpreting the text, or missing something? More information at this link: https://mariadb.com/kb/en/microseconds-in-mariadb/ – meshy Nov 29 '21 at 22:19
  • 1
    you are correct - my apology - the confusing thing is they talk about "microsecond" to mean anything after the decimal; however that is not intuitive with the common difference between "millisecond" and "microsecond"! – Tommy Nov 30 '21 at 00:02
  • 1
    What the hell Accuracy Rounded to `000`, `003` and `007`... I'm not proud Microsoft. – jsgoupil Jun 09 '23 at 15:52
0

The most important piece of information is the one you missed in the original post: which database? Since you later mentioned that it's MariaDB, here you go:

Source: https://mariadb.com/kb/en/mariadb/microseconds-in-mariadb/

Since MariaDB 5.3, the TIME, DATETIME, and TIMESTAMP types, along with the temporal functions, CAST and dynamic columns, have supported microseconds. The datetime precision of a column can be specified when creating the table with CREATE TABLE, for example:

CREATE TABLE example(   col_microsec DATETIME(6),   col_millisec TIME(3) );

Generally, the precision can be specified for any TIME, DATETIME, or TIMESTAMP column, in parentheses, after the type name. The datetime precision specifies number of digits after the decimal dot and can be any integer number from 0 to 6. If no precision is specified it is assumed to be 0, for backward compatibility reasons.

Another example:

SELECT CAST('2009-12-31 23:59:59.998877' as DATETIME(3));
-> 2009-12-31 23:59:59.998
LFLFM
  • 790
  • 7
  • 16
0

When using sqlalchemy with SQLite, the default DateTime column type doesn't store fractional seconds. Instead, you have to use the SQLite dialect DATETIME type: https://docs.sqlalchemy.org/en/14/dialects/sqlite.html#sqlalchemy.dialects.sqlite.DATETIME

Marthinwurer
  • 116
  • 1
  • 6