yaml importer uses incorrect timestamps when the server isn't in UTC

[prandla]

cms/cmscontrib/loaders/italy_yaml.py

Lines 127 to 131 in fcb70ee

	def parse_datetime(val):
	if isinstance(val, datetime):
	return val.astimezone(timezone.utc)
	if isinstance(val, (int, float)):
	return datetime.fromtimestamp(val, timezone.utc)

the yaml importer creates aware datetime objects with the timezone set to utc. sqlalchemy doesn't seem to enjoy these:

>>> import cms.db
>>> from datetime import datetime, timezone
>>> session = cms.db.Session()
>>> contest = cms.db.Contest.get_from_id(1, session)
>>> contest.start = datetime(2025, 9, 28, tzinfo=timezone.utc)
>>> contest.start
datetime.datetime(2025, 9, 28, 0, 0, tzinfo=datetime.timezone.utc)
>>> session.commit()
>>> contest.start
datetime.datetime(2025, 9, 28, 3, 0)

short term fix: we need to convert these aware datetimes to naive before passing them to sqlalchemy. longer term fix: we should probably be using aware datetimes instead... and possibly upgrading our sqlalchemy (it already throws warnings about deprecated datetime methods)

[gollux]

Will naive datetimes help? CMS stores absolute time in the DB anyway, so naive datetime has to be converted at some point. I suggest parsing the input as aware datetime in the current time zone instead.

[prandla]

I suggest parsing the input as aware datetime in the current time zone instead.

that wouldn't give the correct answer either. the problem is that sqlalchemy (or perhaps psycopg?) converts the aware datetime to naive before storing it in the DB, and the naive timestamp will be local time, whereas we want to store UTC times in the DB.

i just read a bunch of python and postgres documentation about timezones and now i'm not even sure what the "correct" approach even is. changing all of the DB columns to timestamp with time zone and configuring postgres/psycopg to use UTC, i guess? (this should make sqlalchemy return aware UTC datetimes, i think.)

[gollux]

I agree with moving to timestamp with time zone.