2

I am having timestamps with time zone in my DB. These are always created in UTC.

I need to display the time to users in Europe/Berlin. Right now I would need to modify the time everywhere with JS or Jinja2 to display to correct time. I feel like there should be a global solution, but I cant find one.

This is an example (models):

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

and here is the code if a user is created:

new_user = User(date_added=datetime.today())

This os how it looks like in the DB:

enter image description here

Once I worked with a MySQL DB and I was using this:

db_session.execute("SET SESSION time_zone = 'Europe/Berlin'")

Is there something similar for posgreSQL?

Roman
  • 3,563
  • 5
  • 48
  • 104
  • You mean a [`SET`](https://www.postgresql.org/docs/current/sql-set.html) command that lets you specify configuration parameters per session or per transaction? Yep, [PostgreSQL has something like that](http://initd.org/psycopg/docs/usage.html#time-zones-handling) :) – shmee Jan 29 '19 at 13:01
  • Possible duplicate of [postgres default timezone](https://stackoverflow.com/questions/6663765/postgres-default-timezone) – shmee Jan 29 '19 at 13:01
  • If you read from a table with value already set, and in a timestamp with time zone on top of that, maybe try `select at time zone 'Europe/Berlin'`. I think setting the session parameter is useful but only for new records. – FXD Jan 29 '19 at 13:29
  • Sadly I cant further comment, but none of the solutions in the duplicate post work, the pgadmin versions are too old. And setting timezone via query tool only alters the current table, also after restarting its back to UTC. – Roman Jan 29 '19 at 13:44
  • NVM. found it, the accepted answer was wrong: `ALTER DATABASE postgres SET timezone TO 'Europe/Berlin';` – Roman Jan 29 '19 at 13:50
  • Ah, you were looking for a persistent approach. Sorry, the MySQL example let me to believe you were looking for sth. on a per-session basis. @lau: PostgreSQL stores aware dates and times in UTC internally. The TimeZone parameter (either set via postgresql.conf or via `SET`) tells PostgreSQL how to _display_ those to clients; it does a conversion from UTC without changing the actual records. Therefore, it also has an effect on existing records. – shmee Jan 29 '19 at 14:20
  • @shmee, this is interesting. Are you saying a timestamp without time zone and a timestamp with time zone are both stored as UTC internally? then how does it know what time zone to show when we select? It can't be the current session time zone, can it? – FXD Jan 29 '19 at 14:23
  • @lau No :) _Naive_ dates and times are stored as such. PostgreSQL does not implicitly assume an offset. _Aware_ dates and times are stored in UTC (given the destination column is a `with tz` data type), because then the offset to convert to UTC is explictily provided. The server 'knows' how to show such dates and times based on the [TimeZone](https://www.postgresql.org/docs/9.2/runtime-config-client.html#GUC-TIMEZONE) parameter ([defaults to GMT as of 9.2](https://serverfault.com/a/554384)) unless instructed otherwise for a given scope via `SET TIME ZONE `. – shmee Jan 29 '19 at 14:38
  • @shmee then I'm not sure I understand your comment. The screenshot provided in the question shows `timestamp with time zone` as the data type. wouldn't that mean a `select ... at time zone ...` would just do the correct conversion based on the field, not on the session parameter? – FXD Jan 29 '19 at 14:42
  • @lau Yes ... you explicitly tell it to disregard the session parameter (or, if that has not been `SET`, the DB parameter) in favor of another, more specific value. Just as much as a value set for the session will take precedence over that configured for the DB. But how does that invalidate my point? – shmee Jan 29 '19 at 16:51

1 Answers1

0

I think that your database should be agnostic to timezones. I usually call this function before showing a datetime from my PostgreSQL database:

from pytz import timezone, UTC

def to_user_timezone(dt):
    """Convert a datetime object to the user's timezone.  If you need
    to convert a :class:`datetime.datetime` object at any time to the user's
    timezone (as returned by :func:`get_timezone` this function can be used).
    """
    if dt.tzinfo is None:
        dt = dt.replace(tzinfo=UTC)
    tzinfo = timezone('Europe/Berlin')
    return tzinfo.normalize(dt.astimezone(tzinfo))
j2logo
  • 649
  • 4
  • 9