tz_test --> demonstrates that pg and sqlite behavior might differ with datetime cols and tz aware python datetimes

tz_test.py

import datetime as dt
from sqlalchemy import create_engine, Column, DateTime, Integer, String
from sqlalchemy.orm import declarative_base, sessionmaker

Base = declarative_base()

class TZTest(Base):
  __tablename__ = 'tztest'
  id = Column(Integer, primary_key=True)
  timestamp_no_tz = Column(DateTime)
  timestamp_with_tz = Column(DateTime(timezone=True))
  note = Column(String)


def test_tz(driver: str = "sqlite"):
  try:
    if driver == "sqlite":
      engine = create_engine("sqlite:///test_db.sqlite3")
    elif driver == "postgres":
      engine = create_engine(
        'postgresql://postgres:postgres@localhost:5432/test_db')
    else:
      raise ValueError("driver option invalid")
  except Exception as e:
    print('Unable to connect to database')
    print(e)
    return

  try:
    Base.metadata.drop_all(engine)
    Base.metadata.create_all(engine)
  except Exception as e:
    print('Unable to create table')
    print("Usually this is because the db 'test_db' doesn't exist")
    print(e)
    return


  # make session
  session = sessionmaker(bind=engine)()

  # add data
  ts_now_with_tz = dt.datetime.now(dt.UTC)
  ts_now_niave = dt.datetime.now(dt.UTC).replace(tzinfo=None)

  for note, ts in [
    ("Insert ts utc with tz", ts_now_with_tz),
    ("Insert ts utc naive", ts_now_niave)]:
    session.add(TZTest(timestamp_no_tz=ts, timestamp_with_tz=ts, note=note))
  session.commit()

  for row in session.query(TZTest).all():
    print(
      f" {row.note} -->  TS no TZ: {row.timestamp_no_tz}, "
      f"TS with TZ: {row.timestamp_with_tz}")

if __name__ == '__main__':
  drivers = ["sqlite", "postgres"]
  for d in drivers:
    print(f"Testing {d}")
    test_tz(d)
    print()

Output example:

(venv) C:\dev\tz_example>py tz_example.py
Testing sqlite
 Insert ts utc with tz -->  TS no TZ: 2024-01-15 18:37:17.068695, TS with TZ: 2024-01-15 18:37:17.068695
 Insert ts utc naive -->  TS no TZ: 2024-01-15 18:37:17.071204, TS with TZ: 2024-01-15 18:37:17.071204

Testing postgres
 Insert ts utc with tz -->  TS no TZ: 2024-01-15 13:37:17.193354, TS with TZ: 2024-01-15 13:37:17.193354-05:00
 Insert ts utc naive -->  TS no TZ: 2024-01-15 18:37:17.193354, TS with TZ: 2024-01-15 18:37:17.193354-05:00

I believe that the timezone aware python datetime object is first converted to local time before having its timezone stripped to fit in the "timestamp with no timezone" postgres column, which I didn't expect but may be expected behavior. This doesn't happen with sqlite, but I'm not familiar with the available time types in sqlite yet.

This is due to the server / database time zone. Postgres uses it to convert the incoming datetime with time zone object to the server time zone before storing. Setting the server time zone to UTC in this case removes the difference.

Testing postgres
Insert ts utc with tz --> TS no TZ: 2024-01-15 13:37:17.193354, TS with TZ: 2024-01-15 13:37:17.193354-05:00

The TS with TZ result was a surprise to me. Is this because of the database's timezone setting that you were discussing in https://github.com/farsounder/python/pull/241#issuecomment-1892772495 ?