CODEWITHSUNDEEP
pythonsqldatetimems-accesspyodbc
SydneyDecoto
SydneyDecoto

Reputation: 13

Pyodbc not returning precise datetime data

I'm using pyodbc in Python 3.4.3 to read data from a Microsoft Access database (.mdb). The query that I'm using to pull time stamps is almost working - I get the correct date and hour/minute/seconds data, but the database contains times that are precise to the hundredth of a second. My time stamps appear to be coming back truncated. Does anyone know how I could get the fractions of a second that are being left off?

Code

t = cursor.execute('SELECT Time FROM ConditionData')

for record in t:
    print(record)

Output:

(datetime.datetime(2013, 7, 27, 8, 24, 54), )
(datetime.datetime(2013, 7, 27, 8, 27, 48), )
(datetime.datetime(2013, 7, 27, 8, 28, 1), )
(datetime.datetime(2013, 7, 27, 8, 29, 29), )
(datetime.datetime(2013, 7, 27, 8, 32, 44), )

Actual timestamps (seconds not truncated):

7/27/2013 8:24:54.27
7/27/2013 8:27:48.95
7/27/2013 8:28:01.97
7/27/2013 8:29:29.12
7/27/2013 8:32:44.40

Upvotes: 1

Views: 2695

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123399

A Date/Time column in Access has a nominal resolution of one second, and the Access ODBC driver will discard fractional seconds when converting a Date/Time value from its internal representation (a Double). So, for example, if we have the following value in a datetime column of a SQL Server table

2014-08-12 01:02:03.670

and we perform the following query on an ODBC linked table in Access

import pyodbc

connStr = (
    r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};"
    r"DBQ=C:\Users\Public\Database1.accdb;"
    )
cnxn = pyodbc.connect(connStr)
crsr = cnxn.cursor()

sql = """\
SELECT [Time] FROM ConditionData WHERE ID=1
"""
crsr.execute(sql)
row = crsr.fetchone()
mydatetime = row[0]

print(repr(mydatetime))
print(mydatetime)

crsr.close()
cnxn.close()

we see

datetime.datetime(2014, 8, 12, 1, 2, 3)
2014-08-12 01:02:03

However, if we tweak our query and use the CDbl() function to return the underlying Double value then we can convert it to a datetime and preserve the fractional seconds like so:

from datetime import datetime, timedelta
import math
import pyodbc

def VT_DATE_double_to_datetime(dbl):
    # math.modf() returns (<fractional_part>, <integer_part>)
    day_parts = math.modf(dbl)
    return (
        datetime(1899, 12, 30) +
        timedelta(days=day_parts[1]) +
        timedelta(seconds=abs(day_parts[0]) * 86400)
        )


connStr = (
    r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};"
    r"DBQ=C:\Users\Public\Database1.accdb;"
    )
cnxn = pyodbc.connect(connStr)
crsr = cnxn.cursor()

sql = """\
SELECT CDbl([Time]) AS ddbl FROM ConditionData WHERE ID=1
"""
crsr.execute(sql)
row = crsr.fetchone()
mydatetime = VT_DATE_double_to_datetime(row[0])

print(repr(mydatetime))
print(mydatetime)

crsr.close()
cnxn.close()

which gives us

datetime.datetime(2014, 8, 12, 1, 2, 3, 670000)
2014-08-12 01:02:03.670000

Upvotes: 1

Related Questions