import sqlite3
import datetime
import sys

def adapt_mydt(val):
    print "ADAPTING", val
    ret = val.strftime("%Y-%m-%d %H:%M:%S") + (".%(msec)06u" %{'msec': val.microsecond} + val.strftime(" %Z"))
    return ret

def convert_mytimes(val):
    print "CONVERTING", val
    dandt = val.split(" ")
    datepart = dandt[0]
    timepart = dandt[1]

    year, month, day = map(int, datepart.split("-"))
    timepart_full = timepart.split(".")
    hours, minutes, seconds = map(int, timepart_full[0].split(":"))
    if len(timepart_full) == 2:
        microseconds = int(float("0." + timepart_full[1]) * 1000000)
    else:
        microseconds = 0

    ret = datetime.datetime(year, month, day, hours, minutes, seconds, microseconds)
    return ret

if __name__ == "__main__":
    print ("sys.version")
    print (sys.version)
    print("sqlite3.version")
    print(sqlite3.version)
    print("sqlite3.sqlite_version")
    print(sqlite3.sqlite_version)
    sqlite3.register_adapter(datetime.datetime, adapt_mydt)
    sqlite3.register_converter("TIMESTAMP", convert_mytimes)

    db = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
    cs = db.cursor()
    cs.execute("create table demo (alpha timestamp)")
    cs.execute("create table demoB (alpha timestamp)")
    cs.executemany(
        "insert into demo (alpha) values (?)",
        [
            ["2014-02-10 20:00:00.000000 UTC"],
            [datetime.datetime.now()],
            ])

    for row in cs.execute("select * from (select alpha from demo union all select alpha from demoB) order by alpha"):
        print row
"""
sys.version
2.7.2 (default, Jun 24 2011, 12:21
sqlite3.version
2.6.0
sqlite3.sqlite_version
3.6.21
ADAPTING 2014-02-12 10:11:22.09700
(u'2014-02-10 20:00:00.000000 UTC'
(u'2014-02-12 10:11:22.097000 ',)


2.5.1 (r251:54863, May  1 2007, 17:47:05) [MSC v.1310 32 bit (Intel)]
sqlite3.version
2.3.2
sqlite3.sqlite_version
3.3.4
ADAPTING 2014-02-12 10:14:49.886000
CONVERTING 2014-02-10 20:00:00.000000 UTC
CONVERTING 2014-02-12 10:14:49.886000 
(datetime.datetime(2014, 2, 10, 20, 0),)
(datetime.datetime(2014, 2, 12, 10, 14, 49, 886000),)
"""