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

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 ?