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. --- coffee_db.py | 14 +++++++------- 1 file changed, 7 insertions(+), 7 deletions(-) diff --git a/coffee_db.py b/coffee_db.py index 8c18e63..d3f659c 100644 --- 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 " + -- 2.7.4