Adding Epoch Time to Database
Not pretty but it works…
After a power cut had stopped the data recording on my home monitor I decided to follow up on my earlier post of storing the date in epoch format instead of just a date/time string by converting the existing db to add an epoch column, what follows is not pretty & probably not something anyone should follow. It’s cobbled together from my beginners knowledge and of course internet searches…
I added an extra column manually using DB Browser for SQLite and then programmatically converted the combined date and time into epoch
it took time for the program to loop through 71869 records
python code snippet start
# -*- coding: utf-8 -*-
from datetime import date, datetime
import sqlite3
import numpy
import pandas as pd
import matplotlib.pyplot as plt
import datetime
import time
conn=sqlite3.connect('home_backup.sqlite')
curs=conn.cursor()
# pd.set_option('display.max_rows', None)
# get last row
# print (df)
curs.execute("SELECT * FROM home_mon")
rws = curs.fetchall()
for row in rws:
dt = row[2]
tm = row[3]
print (dt,tm)
tstr = dt +' ' + tm
dtobj = datetime.datetime.strptime(tstr, '%Y-%m-%d %H:%M:%S')
start_epoch = int(round(time.mktime(dtobj.timetuple())))
print(start_epoch)
curs.execute("UPDATE home_mon SET EPOCH = ?",(start_epoch,))
curs.commit()
conn.close()python code snippet end
Before anyone shouts yes i know the above does not work :tired_face:, eventually after it stopped running i realised i had simply told the UPDATE command to update every row so the above will change not only the row value its on but everything in the column to the current value. No wonder it took so long.
python code snippet start
# -*- coding: utf-8 -*-
from datetime import date, datetime
import sqlite3
import numpy
import pandas as pd
import matplotlib.pyplot as plt
import datetime
import time
conn=sqlite3.connect('home_backup.sqlite')
curs=conn.cursor()
# pd.set_option('display.max_rows', None)
# get last row
# print (df)
curs.execute("SELECT * FROM home_mon")
rws = curs.fetchall()
#rws = curs.fetchmany(20)
for row in rws:
#print(row)
r = row[0]
dt = row[2]
tm = row[3]
print (r,dt,tm)
tstr = dt +' ' + tm
dtobj = datetime.datetime.strptime(tstr, '%Y-%m-%d %H:%M:%S')
start_epoch = int(round(time.mktime(dtobj.timetuple())))
# print(dt,tm,start_epoch)
curs.execute("UPDATE home_mon SET EPOCH = ? WHERE IDX = ?",(start_epoch,r))
conn.commit()
conn.close()python code snippet end
The update query needed to know which row to update so the important part is the WHERE statement.