Python datetimes in sqlite3

Question

I have a weird problem with sqlite3 datetime objects in Python 2.7. Running this example:

import sqlite3
import datetime

con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
cur = con.cursor()
cur.execute("create table test(d date, ts timestamp)")

today = datetime.date.today()
now = datetime.datetime.now()

cur.execute("insert into test(d, ts) values (?, ?)", (today, now))
cur.execute("select d, ts from test")
row = cur.fetchone()
print today, "=>", row[0], type(row[0])
print now, "=>", row[1], type(row[1])

cur.execute('select current_date as "d [date]", current_timestamp as "ts [timestamp]"')
row = cur.fetchone()
print today, "=>", row[0], type(row[0])
print now, "=>", row[1], type(row[1])

gives me this output:

2012-02-10 => 2012-02-10 <type 'datetime.date'>
2012-02-10 08:17:10.222291 => 2012-02-10 08:17:10.222291 <type 'datetime.datetime'>
2012-02-10 => 2012-02-09 <type 'datetime.date'>
2012-02-10 08:17:10.222291 => 2012-02-09 19:17:10 <type 'datetime.datetime'>

The datetime retrived when using the PARSE_COLNAMES method seems to be wrong. Why is that?

Note this example is from the Python docs

Travis Vaught · Accepted Answer · 2012-02-09 20:15:41Z

From the output you show, it looks like you are in the New Zealand timezone (UTC-12 or UTC-11 with daylight savings time being observed). The problem is how PARSE_COLNAMES is using the converter to python types -- one is in UTC and one is using timezone information available for your localtime (and, yes, I would call this a bug in the converters).

See below the adapters I use for stock price data feeds to consistently convert data for a timezone that I know (you may adjust it to match your timezone, or add some code to adjust for the detected timezone):

def adapt_datetime(dt):
    # Get the datetime for the POSIX epoch.
    epoch = datetime.datetime.utcfromtimestamp(0.0)
    elapsedtime = dt - epoch
    # Calculate the number of milliseconds.
    seconds = float(elapsedtime.days)*24.*60.*60. + float(elapsedtime.seconds) + float(elapsedtime.microseconds)/1000000.0
    return seconds


def convert_datetime(tf):
    # Note: strange math is used to account for daylight savings time and 
    #    times in the Eastern (US) time zone (e.g. EDT)
    tf = float(tf)
    edt_adjustment = 6 * 60. * 60.
    if time.localtime(tf).tm_isdst:
        edt_adjustment = 5 * 60. * 60.
    return datetime.datetime.fromtimestamp(tf+edt_adjustment)

sqlite3.register_adapter(datetime.datetime, adapt_datetime)
sqlite3.register_converter("datetime", convert_datetime)

You may see all this in action in this bit of code on github.

Alex Goulielmos · Accepted Answer · 2021-05-20 16:11:18Z

0

This doesn't have to do with the detect_types parameter. Even if you omit it, the results will be the same. (But the returned types will be 'str' in this case, and not 'date'/'datetime'.)

answered May 20, 2021 at 16:11

Alex Goulielmos

1

2 Comments

StupidWolf Over a year ago

better as a comment

Alex Goulielmos Over a year ago

Can't comment due to Reputation restriction

Collectives™ on Stack Overflow

Python datetimes in sqlite3

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related