1

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:

Creating a DB entry in incognito mode (chrome browser tab) gives a different/wrong time. EDIT: It has nothing to do with incognito mode, both cases happen in normal mode aswell. I have not figured out yet why.

This is the code:

I changed the default time zone of my database:

ALTER DATABASE postgres SET timezone TO 'Europe/Berlin';

the model:

class User(UserMixin, Base):
    __tablename__ = 'users'
    date_added = Column(DateTime(timezone=True), nullable=False)

The method I use to add datetime to DB:

date_added=datetime.today()

How it looks in the DB (my localtime at this time was 13:53:46):

Creating entry not in incognito

timestamp with time zone
2019-02-01 13:53:46.73817+01

Creating entry in incognito

timestamp with time zone
2019-02-01 12:53:46.73817+01

This really makes me worry. It is simply wrong. Even if I would convert the datetime objects to localtime. Both entries have been done at the same time, but show different results, how is this possible?

Also when viewing these dates in HTML, postgreSQL does not apply the offset, so the first date looks correct, but the second is wrong.

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.

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

EDIT

I am saving a datetime everytime a user logs in. Currently I tried this on not incognito. My localtime is 14:13:33 BUT it saved into the DB: 2019-02-01 13:13:33.804339+01. 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.

EDIT

I double checked all tables in question with SHOW timezone; and they all correctly return Europe/Berlin

Roman
  • 3,563
  • 5
  • 48
  • 104
  • 2
    I assume that by `Incognito`, you're referring to a Chrome browser `Incognito window`? Is that correct? – Chris Larson Feb 04 '19 at 18:16
  • Likely the *session time zone* setting is not consistent in your queries. I doubt the incognito window has anything to do with it. – Matt Johnson-Pint Feb 04 '19 at 18:22
  • Possible duplicate of: https://stackoverflow.com/questions/6663765/postgres-default-timezone ? – Matt Johnson-Pint Feb 04 '19 at 18:23
  • Have you set the `timezone =` value in `postgresql.conf`, and if so, how is it set? – Chris Larson Feb 04 '19 at 18:30
  • Yes I am referring to the chrome incognito window. @Chris Larson – Roman Feb 05 '19 at 08:41
  • All I had done is written in the question. To change the time zone I only used: `ALTER DATABASE postgres SET timezone TO 'Europe/Berlin';` I thought Its not necessary to change session timezone, if I have a general rule on DB level. – Roman Feb 05 '19 at 08:41
  • @MattJohnson I saw this thread. That is where I found the solution with alter database. – Roman Feb 05 '19 at 09:00
  • Check out [Difference between timestamps with/without time zone in PostgreSQL](https://stackoverflow.com/questions/5876218/difference-between-timestamps-with-without-time-zone-in-postgresql). `timestamp with time zone` stores the datetime in UTC _NOT in a local time zone_. Your db time zone setting is just a way to tell PostgreSQL to render the timestamp in your locally set time zone rather than UTC. However, it is not clear to me what the source of your issue is based on what you have posted so far. – benvc Feb 08 '19 at 20:00
  • @benvc, I also have still no clue. Atm. I made a break. Will tackle the issue next week with a fresh head. Thanks for the link – Roman Feb 08 '19 at 20:14

1 Answers1

9

datetime.today() returns a timestamp without timezone info of the current local time (the returned value is timezone naive). The root of your problem is that somewhere between SQL Alchemy's postgres adapter and postgres itself it has to guess at a timezone. 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). You can sidestep this entire can of worms by either:

  1. Storing all of your timestamps without a time zone in UTC, then converting to the desired timestamp as appropriate

  2. Always using timestamps with time zone (i.e. 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.

Note that in postgres, the timestamp with time zone type is still always stored as UTC with no extra information, the database simply uses the session-level configuration to decide which time zone to display it as when outputting it.

George S
  • 2,041
  • 9
  • 13
  • Very good explanation. This could really be the issue. I will test it tomorrow. – Roman Feb 10 '19 at 17:35
  • 1
    I replaced all `datetime.today()` with `datetime.now(pytz.timezone('Europe/Berlin'))`. Now it always show the same time, but it also shows a +1 offset in postgres. I assume it tells that this offset WAS already applied. For example if I create at 15:30 localtime an entry it will show in the DB 15:30 +1 offset, which is actually UTC 14:30. So I assume it means 14:30 with the already applied offset. – Roman Feb 11 '19 at 14:46
  • 1
    That is correct -- Postgres shows a timestamp with tz value in the format of showing the time and the offset that it is displaying it at. It stores the timestamp in the db in UTC and will display it adjusted to your configured timezone, showing the offset applied. – George S Feb 11 '19 at 19:41
  • Thank you for confirming this. – Roman Feb 11 '19 at 22:06