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()

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 ?