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.
def list_coffees(uid=None):
c = conn.cursor()
if uid is 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"):
- 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():
print(row)
def flavors():
if uid is None:
res = list(c.execute("""
select strftime('%s', ds.d),count(c.flavor),c.flavor from
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
"""))
left join coffees c
on d = date(c.time) group by d, c.flavor
"""))
res = list(c.execute(
"""
select strftime('%s', ds.d),count(c.flavor),c.flavor from
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
- (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,)))
on d = date(c.time) group by d, c.flavor
"""
, (uid,)))
args.append(uid)
if start is not 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))
- 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 " +
for count, in c.execute(
"select count(*) from coffees where " +