Fix incorrect counting of coffees made during time close to midnight

Datetime in database is stored in UTC timezone. Currently we are in
UTC +2 timezone (GMT +1). Because of time shifts fixing the time
cannot be done in a static way.

Dynamic solution is using paramater 'localtime' in SQL query function
date(). Therefore it is required for the target device to have
properly set timezone.

TODO:
Suggested solution is to use 'type converters' for python/sqlite3.

diff --git a/coffee_db.py b/coffee_db.py
index 8c18e63982bacb3e95fed035af47073d4fe4cd97..d3f659c2d43471adb804e9c155311db08dfb59ff 100644 (file)
--- a/coffee_db.py
+++ b/coffee_db.py
@@ -58,10 +58,10 @@ def add_coffee(uid, flavor, time=None):
 def list_coffees(uid=None):
     c = conn.cursor()
     if uid is None:
-        for row in c.execute("select id, time, flavor from coffees"):
+        for row in c.execute("select id, date(time, 'localtime'), flavor from coffees"):
             print(row)
     else:
-        for row in c.execute("select time, flavor from coffees where id = ?", (uid,)):
+        for row in c.execute("select date(time, 'localtime'), flavor from coffees where id = ?", (uid,)):
             print(row)
 
 def flavors():
@@ -96,7 +96,7 @@ def coffee_history(uid=None):
     if uid is None:
         res = list(c.execute("""
             select strftime('%s', ds.d),count(c.flavor),c.flavor from
-            (select num,date('now',-num || ' days') as d from days) ds
+            (select num,date('now', 'localtime', -num || ' days') as d from days) ds
             left join coffees c
             on d = date(c.time) group by d, c.flavor
             """))
@@ -104,9 +104,9 @@ def coffee_history(uid=None):
         res = list(c.execute(
             """
             select strftime('%s', ds.d),count(c.flavor),c.flavor from
-            (select num,date('now',-num || ' days') as d from days) ds
+            (select num,date('now', 'localtime', -num || ' days') as d from days) ds
             left join
-            (select time,flavor from coffees where id = ?) c
+            (select date(time, 'localtime') as time,flavor from coffees where id = ?) c
             on d = date(c.time) group by d, c.flavor
             """
             , (uid,)))
@@ -125,10 +125,10 @@ def coffee_count(uid=None, start=None, stop=None):
         args.append(uid)
 
     if start is not None:
-        clauses.append("date(time) >= date('now', '-%d days')" % int(start))
+        clauses.append("date(time, 'localtime') >= date('now', 'localtime', '-%d days')" % int(start))
 
     if stop is not None:
-        clauses.append("date(time) <= date('now', '-%d days')" % int(stop))
+        clauses.append("date(time, 'localtime') <= date('now', 'localtime', '-%d days')" % int(stop))
 
     for count, in c.execute(
             "select count(*) from coffees where " +