Get different timezones when writing and reading row with datetime column?

Question

Why I get different timezones when writing and reading row with datetime column?

I have next table Tests:

class Test(Base):
    __tablename__ = 'tests'

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4, unique=True)
    test_datetime = Column(DateTime(timezone=True), nullable=False, default=datetime.utcnow())

    def __init__(self, test_datetime):
        self.test_datetime = test_datetime

    def __repr__(self):
        return f'<Test(id={self.id}, test_datetime={self.test_datetime})'

I decided to store the aware datetime in utc in the database and Tests table. I am performing adding a new row to a table. The test object has aware datetime.

session.add(Test(datetime.utcnow().replace(tzinfo=pytz.UTC)))
session.commit()
session.close()

psql select command output:

database_name=# select * from tests;                                                                                                                      id                  |        test_datetime
--------------------------------------+-----------------------------
 751bcef0-2ef4-4c0f-960c-a40ca2b8ec94 | 2021-04-12 14:46:30.8957+03
(1 row)

database_name=# SHOW timezone ;
   TimeZone
---------------
 Europe/Moscow
(1 row)

database_name=# \d tests
                           Table "public.tests"
    Column     |           Type           | Collation | Nullable | Default
---------------+--------------------------+-----------+----------+---------
 id            | uuid                     |           | not null |
 test_datetime | timestamp with time zone |           | not null |
Indexes:
    "tests_pkey" PRIMARY KEY, btree (id)

We see the datatime column is incorrectly written to the database (timezone offset +3 hour to place +0).

Reading data from the database, as expected, I get a different timezone (+3h)

query = session.query(Test)
for test in query.order_by(Test.test_datetime.asc()).all():
    print(test.test_datetime, test.test_datetime.tzinfo)

Python output:

2021-04-12 14:46:30.895700+03:00 psycopg2.tz.FixedOffsetTimezone(offset=180, name=None)

My database has a TimeZone: "Europe/Moscow". There is an assumption: it is possible that when writing to the database, the datetime is saved as naive. And all objects in the test_datetime column get the timezone (offset) from the database. But I'm not sure if it works like that.

Answer 1

PostgreSQL stores timestamp with time zone values as UTC and by default displays them in the local time zone. So, if I insert a UTC value via psql …

mydb=# insert into tests (id, test_datetime) values (1, '2021-04-12 11:46:30.8957+00');
INSERT 0 1

… and then retrieve it …

mydb=# select * from tests;
 id |        test_datetime        
----+-----------------------------
  1 | 2021-04-12 05:46:30.8957-06
(1 row)

… it is displayed in my local time zone (currently UTC-6). psycopg2 also returns the value in the local time zone

with engine.begin() as conn:
    result = conn.execute(
        sa.text("SELECT test_datetime FROM tests WHERE id=1")
    ).scalar()
    print(type(result))  # <class 'datetime.datetime'>
    print(result)  # 2021-04-12 05:46:30.895700-06:00

If you want the timezone-aware datetime value to be in UTC then just convert it

    result_utc = result.astimezone(timezone.utc)
    print(result_utc)  # 2021-04-12 11:46:30.895700+00:00