import sqlite3 
import time
import datetime


import sys, os, time, math, traceback, shutil


def adapt_mydt(val):
    ''' val - a tz aware datetime object'''
    ret = val.strftime("%Y-%m-%d %H:%M:%S") + (".%(msec)06u" %{'msec': val.microsecond} + val.strftime(" %Z"))
    return ret

def convert_mytimes(val):
    ''' val - char date representation in form "yyyy-mm-dd HH:MM:SS tzname"'''
    dandt = val.split(" ")
    datepart = dandt[0]
    timepart = dandt[1]
# omit timezonefor example
    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__':


    #current working directory of application
    cwd = "C:/home/Stack"

    myDB = cwd + '/Local/localStackDB'
    cell =120
    #************ env cell
    fileName =( cwd + '/StackDebug' + str('%03d' %cell) + '.txt')
    fp = open(fileName,'w')
    fp.write(str(sqlite3.converters.keys()))
    fp.write('\n')
    #****************************************************************
    sqlite3.register_adapter(datetime.datetime,adapt_mydt)
    sqlite3.register_converter("timestamp",convert_mytimes)
    
    fp.write(str(sqlite3.converters.keys()))
    fp.write('\n')
    fp.write("sqlite3.converters['TIMESTAMP']= " + str(sqlite3.converters['TIMESTAMP']))
    fp.write('\n')

    con = sqlite3.connect(myDB, detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)

    cur = con.cursor()

    aQuery= ('select * from (select cell, s_time, e_time, mon_time, err_time, fuel, run_time'+
    ' from hrly_info  where s_time >= "2014-02-10 20:00:00.000000 UTC" and '+
    's_time < "2014-02-10 21:00:00.000000 UTC" and  cell = ' + str(cell) +
    ' union all '+
    'select cell, s_time, e_time, mon_time, err_time, fuel, run_time from back_fill  '+
    'where s_time >= "2014-02-10 20:00:00.000000 UTC" and '+
    's_time < "2014-02-10 21:00:00.000000 UTC" and  cell = '+ str(cell)+ ') order by cell, s_time')
    
    CurrentRows = []
    try:
        cur.execute(aQuery)
        CurrentRows = cur.fetchall()
        retStat = True
    except sqlite3.OperationalError, msg:
        myMsg = " Select hour error " + str(msg)
        print myMsg
    fp.write('aQuery= ' + aQuery)
    fp.write('\n')
    fp.write('CurrentRows ' + str(CurrentRows))
    fp.write('\n')
    fp.close()



--- from 2.5
['DATE', 'TIMESTAMP']
['DATE', 'TIMESTAMP']
sqlite3.converters['TIMESTAMP']= <function convert_mytimes at 0x00832F70>
aQuery= select * from (select cell, s_time, e_time, mon_time, err_time, fuel, run_time from hrly_info  where s_time >= "2014-02-10 20:00:00.000000 UTC" and s_time < "2014-02-10 21:00:00.000000 UTC" and  cell = 5 union all select cell, s_time, e_time, mon_time, err_time, fuel, run_time from back_fill  where s_time >= "2014-02-10 20:00:00.000000 UTC" and s_time < "2014-02-10 21:00:00.000000 UTC" and  cell = 5) order by cell, s_time
CurrentRows [(5, datetime.datetime(2014, 2, 10, 20, 0), datetime.datetime(2014, 2, 10, 20, 10), 600, 0, 0, 0), (5, datetime.datetime(2014, 2, 10, 20, 10), datetime.datetime(2014, 2, 10, 20, 20), 600, 0, 0, 0), (5, datetime.datetime(2014, 2, 10, 20, 20), datetime.datetime(2014, 2, 10, 20, 30), 600, 0, 0, 0), (5, datetime.datetime(2014, 2, 10, 20, 30), datetime.datetime(2014, 2, 10, 20, 40), 600, 0, 0, 0), (5, datetime.datetime(2014, 2, 10, 20, 40), datetime.datetime(2014, 2, 10, 20, 50), 600, 0, 0, 0), (5, datetime.datetime(2014, 2, 10, 20, 50), datetime.datetime(2014, 2, 10, 21, 0), 600, 0, 0, 0)]


--- from 2.7

['DATE', 'TIMESTAMP']
['DATE', 'TIMESTAMP']
sqlite3.converters['TIMESTAMP']= <function convert_mytimes at 0x0194F770>
aQuery= select * from (select cell, s_time, e_time, mon_time, err_time, fuel, run_time from hrly_info  where s_time >= "2014-02-10 20:00:00.000000 UTC" and s_time < "2014-02-10 21:00:00.000000 UTC" and  cell = 120 union all select cell, s_time, e_time, mon_time, err_time, fuel, run_time from back_fill  where s_time >= "2014-02-10 20:00:00.000000 UTC" and s_time < "2014-02-10 21:00:00.000000 UTC" and  cell = 120) order by cell, s_time
CurrentRows [(120, u'2014-02-10 20:00:00.000000 UTC', u'2014-02-10 20:10:00.000000 UTC', 600, 0, 0, 0), (120, u'2014-02-10 20:10:00.000000 UTC', u'2014-02-10 20:20:00.000000 UTC', 600, 0, 0, 0), (120, u'2014-02-10 20:20:00.000000 UTC', u'2014-02-10 20:30:00.000000 UTC', 600, 0, 0, 0), (120, u'2014-02-10 20:30:00.000000 UTC', u'2014-02-10 20:40:00.000000 UTC', 600, 0, 0, 0), (120, u'2014-02-10 20:40:00.000000 UTC', u'2014-02-10 20:50:00.000000 UTC', 600, 0, 0, 0), (120, u'2014-02-10 20:50:00.000000 UTC', u'2014-02-10 21:00:00.000000 UTC', 600, 0, 0, 0)]