0

I have a list of UTC datetimes from my table. I want to get the distinct dates in a local timezone, and get the count of entries for each date- again, for the local timezone.

I'm sure I'm overcomplicating this problem, but I'm pretty new to Python & SQLAlchemy, let alone dates & times & timezones.

I am storing my timestamps in UTC using a column with default=utcnow()... this works great: 2019-07-05 06:19:59.920630

I can even display timestamps in the browser's current timezone as necessary (in other parts of the app).

However I am trying to run an SQLAlchemy query based on the timezone to just get the entries for that user's local day.

2019-07-05 06:19:59.920630 can be converted using my utc_to_local helper function into 2019-07-04 23:19:59.920630... so I need my query to include that datetime entry in my query for all records on "July 4, 2019"

So I was running an SQLAlchemy query to get a list of distinct dates

>>>uniquedays=db.session.query(func.distinct(func.DATE(TableClass.timestamp))).all()

but I just realized this is returning a list of dates that potentially don't have any records for the user's locale ... in my example, July 5th doesn't have any entries for my timezone- but it's being returned as a uniqueday.

So I can also return the datetimelist like so:

>>>datetimelist = db.session.query(TableClass.timestamp).all()

But now I've just got this list of datetime (I assume) tuples:

>>>datetime
[(datetime.datetime(2019, 6, 30, 21, 8, 11, 354018),), (datetime.datetime(2019, 6, 30, 21, 21, 58, 158467),), (datetime.datetime(2019, 6, 30, 23, 39, 8, 310248),)....................

I was using this to convert utc datetime into my local time:

def utc_to_local(utc_dt):
    return utc_dt.replace(tzinfo=timezone.utc).astimezone(tz=None)

I am using SQLite for now, but intend to switch to a MySQL database for production.

Ryan Reese
  • 3
  • 3

1 Answers1

2

Ideally, this would be done at the DB level. In MySQL, it could look something like this:

user_tz = 'PST'
uniquedays = db.session.query(func.distinct(func.DATE(
                 func.convert_tz(TableClass.timestamp, 'UTC', user_tz)))).all()

Unfortunately, however, SQLite doesn't support fully-flexible timezone conversion. So until you switch, you will have to do this in Python. Hopefully you don't have millions of rows?

import arrow
from collections import Counter

datetimelist = db.session.query(TableClass.timestamp).all()
dates = [arrow.get(dt[0]).to('US/Pacific').date() for dt in datetimelist]
counter = Counter(dates)

Another approach would be to use SQLAlchemy's hybrid properties, but it's probably not worth the trouble if you're going to be switching to a real DB shortly anyway.

Nick K9
  • 3,885
  • 1
  • 29
  • 62
  • 1
    hah, definitely not millions of rows barely a playground app at this point :) I understand on the SQLite support... I may as well swap out for MySQL sooner rather than later. That did work though! I will keep playing with it, but I'm at least getting the expected results! – Ryan Reese Jul 05 '19 at 21:22
  • 1
    In case anyone sees this, I got MySQL working properly per Nick K9's first suggestion to make the Database handle the conversion. The refactor went smoothly from SQLite to MySQL, just a few small tweaks. **Important Note** You will need to install the MySQL Timezone tables into your database. [Here's some info on how](https://stackoverflow.com/questions/14454304/convert-tz-returns-null). – Ryan Reese Jul 08 '19 at 16:13
  • @nick-k9 sqlite is a REAL database – manas Jan 05 '21 at 21:19