CODEWITHSUNDEEP
pythonpostgresqltimezonesqlalchemytimestamp
Chris R
Chris R

Reputation: 17926

How do I convert the time stamps from a PostgreSQL DB into user-time-zone times?

I'm using SQLAlchemy and PostgreSQL here, in a web application.

My SQLA model is defined (roughly) as follows:

class MyModel(Base):
    ts_field = Column(DateTime(timezone=True))
    # more here, obviously

My web app users have a timezone property whose format I have not yet specified (that's part of this question) that determines how to display time stamps to them. They're not all in the locale of the server, obviously, so the system time zone isn't exactly useful.

I want to have an easy way to get out timestamps formatted for each user depending on their selected time zone.

(I'm a bit fuzzy on a lot of the terminology of time zone conversion, so if I'm asking this vaguely -- and I'll bet I am! -- please feel free to comment and help me clarify!)

Upvotes: 1

Views: 1608

Answers (2)

Scott Marlowe
Scott Marlowe

Reputation: 8920

OK, when you create a timestamp with timezone (shortcut name timestamptz) pg creates a timestamp, and converts between the current timezone to GMT when storing it.

When you pull it out, you change the timezone setting to whatever you want to change it to some other offset. For example:

 smarlowe=# set timezone='est';
 SET
 smarlowe=# select now();
               now              
 2010-11-30 00:38:32.683625-05
 (1 row)

 smarlowe=# set timezone='mst'; 
 SET
 smarlowe=# select now();
               now              
 2010-11-29 22:38:48.293708-07

You can shorten this up by using "at time zone" nomenclature:

smarlowe=# set timezone='mst';
SET
smarlowe=# select now();
              now              
-------------------------------
 2010-11-29 22:40:15.854833-07
(1 row)

smarlowe=# select now() at time zone 'est';
          timezone          
----------------------------
 2010-11-30 00:40:17.243828

no need to set each time zone over and over, just in the statement each time according to the proper timezone.

Upvotes: 1

nmichaels
nmichaels

Reputation: 51039

You can use pytz to do the conversions.

Upvotes: 0

Related Questions