sqlalchemy postgresql timestamp operators

#Sqlalchemy postgresql timestamp operators code#

replace datetime.today() with datetime.now() and pass in the desired time zone so that you are always dealing with a timezone aware value ), so there is no need for the computer to assume a timezone.

Storing all of your timestamps without a time zone in UTC, then converting to the desired timestamp as appropriateĪlways using timestamps with time zone (i.e. You can sidestep this entire can of worms by either: As you might imagine, computer systems tend toward assuming UTC if no timezone is explicitly provided, but the precise logic for a toolsuite set can be complex and hard to debug (and my depend on your local time zone settings on your computer, system-level settings in the db, session level settings, and the preference of the toolmaker). The root of your problem is that somewhere between SQL Alchemy's postgres adapter and postgres itself it has to guess at a timezone. I double checked all tables in question with SHOW timezone and they all correctly return Europe/Berlinĭatetime.today() returns a timestamp without timezone info of the current local time (the returned value is timezone naive). How is this even possible? I know it cant be random, but right now it looks like its saving times randomly sometimes UTC with offset and sometimes Europe/Berlin with offset. My localtime is 14:13:33 BUT it saved into the DB: I am saving a datetime everytime a user logs in.

#Sqlalchemy postgresql timestamp operators code#

I also have double checked my code everywhere, I am not using other methods to manipulate datetime objects.

Initially I just wanted to find a way to store ALL datetime objects in Europe/Berlin and return them in Europe/Berlin time, so I dont have to convert UTC to Europe/Berlin, but now I think something went horribly wrong. Both entries have been done at the same time, but show different results, how is this possible?Īlso when viewing these dates in HTML, postgreSQL does not apply the offset, so the first date looks correct, but the second is wrong. Even if I would convert the datetime objects to localtime. How it looks in the DB (my localtime at this time was 13:53:46):Ĭreating entry not in incognito timestamp with time zoneĬreating entry in incognito timestamp with time zone The method I use to add datetime to DB: date_added=datetime.today() I changed the default time zone of my database: ALTER DATABASE postgres SET timezone TO 'Europe/Berlin' ĭate_added = Column(DateTime(timezone=True), nullable=False)

I am using flask sqlalchemy and postgreSQL and I have issues with the displayed datetimes, while investigating this issue I found an other weird thing:Ĭreating a DB entry in incognito mode (chrome browser tab) gives a different/wrong time.ĮDIT: It has nothing to do with incognito mode, both cases happen in normal mode aswell.