I know there are quite a few questions about Date/Time with Flask/SQLAlchemy and Postgres, but I have a problem that I just can't quite figure out. So, with my project I am downloading statistics from ESPN. All their game times, rightly so, are in UTC format. Example: 2020-11-26T02:56Z So, I add this to me DB in a field of "timestamp with time zone" which I've learn is almost the standard. So, my issue I have is I'm trying to display a list of what games are "today" and I'm in the central time zone of America. I don't know how to offset my filter request for this. So, two questions:
- should I keep the db format of timestamp with time zone and then figure out a way to filter those based on current day, central time zone, of those games or
- just convert the game times to Central Time before inserting them into my db. I don't foresee using this app outside of my current time zone, but I'm not 100% sure of that.
Am I just missing something obviously easy? When I view the db in PG Admin 4, the date/times are my timezone (db is on my laptop), but the api I'm writing returns json with it being UTC time.
This is what I originally tried, but obviously didn't work:
events = Event.query.filter(Event.gamedate > pytz.utc.localize(datetime.today()- timedelta(days=1)),Event.gamedate < pytz.utc.localize(datetime.today() timedelta(days=1))).all()
CodePudding user response：
When you load an UTC timestamp into timestamp with time zone, you have to set the timezone parameter correctly in that session:
SET timezone = 'UTC';