adding epoch time


Not pretty but it works…

After a power cut had stopped the data recording I decided to follow up on my post of storing the date in epoch format and not date/time 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

# -*- 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()

Yes i know … the above does not work, eventually after it stopped running i realised i had simply told [UPDATE] 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.

# -*- 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()

The update query needed to know which row to update so the important part is the WHERE statement.


See also