Coverage for tests/test_datetime.py: 100%
216 statements
« prev ^ index » next coverage.py v7.17.2, created at 2026-11-16 00:02 +0000
« prev ^ index » next coverage.py v7.17.2, created at 2026-11-16 00:02 +0000
1from collections.abc import Callable 1abcdefghijklm
2from datetime import UTC, date, datetime, time, timedelta, timezone, tzinfo 1abcdefghijklm
3from io import StringIO 1abcdefghijklm
4from operator import add, sub 1abcdefghijklm
5from typing import Annotated, Any 1abcdefghijklm
7import pytest 1abcdefghijklm
8from alembic.autogenerate import compare_metadata, render_python_code 1abcdefghijklm
9from alembic.migration import MigrationContext 1abcdefghijklm
10from alembic.operations import Operations, ops 1abcdefghijklm
11from pydantic import AwareDatetime, NaiveDatetime, ValidationError 1abcdefghijklm
12from sqlalchemy import Column, Date, DateTime, Engine, Interval, Time 1abcdefghijklm
13from sqlalchemy.dialects import mysql, postgresql, sqlite 1abcdefghijklm
14from sqlalchemy.exc import StatementError 1abcdefghijklm
15from sqlalchemy.schema import CreateTable 1abcdefghijklm
16from sqlmodel import ( 1abcdefghijklm
17 Field,
18 Session,
19 SQLModel,
20 UTCDateTime,
21 create_engine,
22 select,
23 update,
24)
27@pytest.mark.parametrize("nullable", [False, True]) 1abcdefghijklm
28@pytest.mark.parametrize( 1abcdefghijklm
29 "annotation, timezone_enabled, error_type",
30 [
31 (datetime, True, None),
32 (AwareDatetime, True, "timezone_aware"),
33 (NaiveDatetime, False, "timezone_naive"),
34 (Annotated[datetime, AwareDatetime], True, "timezone_aware"),
35 (Annotated[datetime, NaiveDatetime], False, "timezone_naive"),
36 (Annotated[datetime, AwareDatetime()], True, "timezone_aware"),
37 (Annotated[datetime, NaiveDatetime()], False, "timezone_naive"),
38 (Annotated[NaiveDatetime, AwareDatetime], True, "timezone_aware"),
39 (Annotated[AwareDatetime, NaiveDatetime], False, "timezone_naive"),
40 (Annotated[datetime, NaiveDatetime, AwareDatetime], True, "timezone_aware"),
41 (Annotated[datetime, AwareDatetime, NaiveDatetime], False, "timezone_naive"),
42 (Annotated[datetime, "other metadata"], True, None),
43 ],
44)
45def test_datetime_types( 1abcdefghijklm
46 annotation: Any, timezone_enabled: bool, error_type: str | None, nullable: bool
47) -> None:
48 if nullable: 1ABCDEFGHIJKLM
49 annotation = annotation | None 1ABCDEFGHIJKLM
51 class Event(SQLModel, table=True): 1ABCDEFGHIJKLM
52 id: int | None = Field(default=None, primary_key=True) 1ABCDEFGHIJKLM
53 occurred_at: annotation 1ABCDEFGHIJKLM
55 column = SQLModel.metadata.tables["event"].c.occurred_at 1ABCDEFGHIJKLM
56 assert isinstance(column.type, UTCDateTime if timezone_enabled else DateTime) 1ABCDEFGHIJKLM
57 assert column.type.timezone is timezone_enabled 1ABCDEFGHIJKLM
58 assert column.nullable is nullable 1ABCDEFGHIJKLM
60 aware = datetime(2026, 1, 1, 12, tzinfo=timezone(timedelta(hours=2))) 1ABCDEFGHIJKLM
61 naive = datetime(2026, 1, 1, 12) 1ABCDEFGHIJKLM
62 valid = aware if timezone_enabled else naive 1ABCDEFGHIJKLM
63 assert Event.model_validate({"occurred_at": valid}).occurred_at == valid 1ABCDEFGHIJKLM
64 if error_type is None: 1ABCDEFGHIJKLM
65 assert Event.model_validate({"occurred_at": naive}).occurred_at == naive 1ABCDEFGHIJKLM
66 else:
67 invalid = naive if timezone_enabled else aware 1ABCDEFGHIJKLM
68 with pytest.raises(ValidationError) as exc_info: 1ABCDEFGHIJKLM
69 Event.model_validate({"occurred_at": invalid}) 1ABCDEFGHIJKLM
70 assert exc_info.value.errors()[0]["type"] == error_type 1ABCDEFGHIJKLM
71 if nullable: 1ABCDEFGHIJKLM
72 assert Event.model_validate({"occurred_at": None}).occurred_at is None 1ABCDEFGHIJKLM
75def test_inherited_annotated_datetime() -> None: 1abcdefghijklm
76 class EventBase(SQLModel): 22b3b4b5b6b7b8b9b!b#b$b%b'b
77 occurred_at: Annotated[datetime, NaiveDatetime, Field(index=True)] 22b3b4b5b6b7b8b9b!b#b$b%b'b
79 class Event(EventBase, table=True): 22b3b4b5b6b7b8b9b!b#b$b%b'b
80 id: int | None = Field(default=None, primary_key=True) 22b3b4b5b6b7b8b9b!b#b$b%b'b
82 column = SQLModel.metadata.tables["event"].c.occurred_at 22b3b4b5b6b7b8b9b!b#b$b%b'b
83 assert column.type.timezone is False 22b3b4b5b6b7b8b9b!b#b$b%b'b
84 assert column.index is True 22b3b4b5b6b7b8b9b!b#b$b%b'b
87@pytest.mark.parametrize("annotation", [datetime, AwareDatetime, NaiveDatetime]) 1abcdefghijklm
88@pytest.mark.parametrize("timezone_enabled", [False, True]) 1abcdefghijklm
89@pytest.mark.parametrize("use_column", [False, True]) 1abcdefghijklm
90def test_explicit_datetime_type( 1abcdefghijklm
91 annotation: Any, timezone_enabled: bool, use_column: bool
92) -> None:
93 sa_type = DateTime(timezone=timezone_enabled) 2PbQbRbSbTbUbVbWbXbYbZb0b1b
94 if use_column: 2PbQbRbSbTbUbVbWbXbYbZb0b1b
95 field = Field(sa_column=Column(sa_type)) 2PbQbRbSbTbUbVbWbXbYbZb0b1b
96 else:
97 field = Field(sa_type=sa_type) 2PbQbRbSbTbUbVbWbXbYbZb0b1b
99 class Event(SQLModel, table=True): 2PbQbRbSbTbUbVbWbXbYbZb0b1b
100 id: int | None = Field(default=None, primary_key=True) 2PbQbRbSbTbUbVbWbXbYbZb0b1b
101 occurred_at: annotation = field 2PbQbRbSbTbUbVbWbXbYbZb0b1b
103 assert SQLModel.metadata.tables["event"].c.occurred_at.type is sa_type 2PbQbRbSbTbUbVbWbXbYbZb0b1b
106def test_explicit_datetime_type_class() -> None: 1abcdefghijklm
107 class Event(SQLModel, table=True): 2[b]b^b_b`b{b|b}b~bacbcccdc
108 id: int | None = Field(default=None, primary_key=True) 2[b]b^b_b`b{b|b}b~bacbcccdc
109 occurred_at: datetime = Field(sa_type=DateTime) 2[b]b^b_b`b{b|b}b~bacbcccdc
111 assert SQLModel.metadata.tables["event"].c.occurred_at.type.timezone is False 2[b]b^b_b`b{b|b}b~bacbcccdc
114def test_other_temporal_types() -> None: 1abcdefghijklm
115 class Event(SQLModel, table=True): 2CbDbEbFbGbHbIbJbKbLbMbNbOb
116 id: int | None = Field(default=None, primary_key=True) 2CbDbEbFbGbHbIbJbKbLbMbNbOb
117 day: date 2CbDbEbFbGbHbIbJbKbLbMbNbOb
118 at: time 2CbDbEbFbGbHbIbJbKbLbMbNbOb
119 duration: timedelta 2CbDbEbFbGbHbIbJbKbLbMbNbOb
121 columns = SQLModel.metadata.tables["event"].c 2CbDbEbFbGbHbIbJbKbLbMbNbOb
122 assert isinstance(columns.day.type, Date) 2CbDbEbFbGbHbIbJbKbLbMbNbOb
123 assert isinstance(columns.at.type, Time) 2CbDbEbFbGbHbIbJbKbLbMbNbOb
124 assert isinstance(columns.duration.type, Interval) 2CbDbEbFbGbHbIbJbKbLbMbNbOb
127@pytest.mark.parametrize( 1abcdefghijklm
128 "dialect, aware_type, naive_type",
129 [
130 (
131 postgresql.dialect(),
132 "TIMESTAMP WITH TIME ZONE",
133 "TIMESTAMP WITHOUT TIME ZONE",
134 ),
135 (mysql.dialect(), "DATETIME", "DATETIME"),
136 (sqlite.dialect(), "DATETIME", "DATETIME"),
137 ],
138)
139def test_datetime_column_types(dialect: Any, aware_type: str, naive_type: str) -> None: 1abcdefghijklm
140 class Event(SQLModel, table=True): 2(b)b*b+b,b-b.b/b:b;b=b?b@b
141 id: int | None = Field(default=None, primary_key=True) 2(b)b*b+b,b-b.b/b:b;b=b?b@b
142 occurred_at: datetime 2(b)b*b+b,b-b.b/b:b;b=b?b@b
143 local_time: NaiveDatetime 2(b)b*b+b,b-b.b/b:b;b=b?b@b
145 ddl = str(CreateTable(SQLModel.metadata.tables["event"]).compile(dialect=dialect)) 2(b)b*b+b,b-b.b/b:b;b=b?b@b
146 assert f"occurred_at {aware_type} NOT NULL" in ddl 2(b)b*b+b,b-b.b/b:b;b=b?b@b
147 assert f"local_time {naive_type} NOT NULL" in ddl 2(b)b*b+b,b-b.b/b:b;b=b?b@b
150def test_datetime_round_trip(database_engine: Engine) -> None: 1abcdefghijklm
151 class Event(SQLModel, table=True): 1nopqrstuvwxyz
152 id: int | None = Field(default=None, primary_key=True) 1nopqrstuvwxyz
153 occurred_at: datetime 1nopqrstuvwxyz
154 local_time: NaiveDatetime 1nopqrstuvwxyz
155 optional_time: datetime | None = None 1nopqrstuvwxyz
157 aware = datetime(2026, 1, 1, 12, tzinfo=timezone(timedelta(hours=2))) 1nopqrstuvwxyz
158 naive = datetime(2026, 1, 1, 12) 1nopqrstuvwxyz
159 engine = database_engine 1nopqrstuvwxyz
160 SQLModel.metadata.create_all(engine) 1nopqrstuvwxyz
161 with Session(engine) as session: 1nopqrstuvwxyz
162 event = Event(occurred_at=aware, local_time=naive) 1nopqrstuvwxyz
163 session.add(event) 1nopqrstuvwxyz
164 session.flush() 1nopqrstuvwxyz
165 # Binding doesn't change the original value on the model.
166 assert event.occurred_at is aware 1nopqrstuvwxyz
167 session.commit() 1nopqrstuvwxyz
168 session.refresh(event) 1nopqrstuvwxyz
169 assert event.occurred_at == aware 1nopqrstuvwxyz
170 assert event.occurred_at.tzinfo is UTC 1nopqrstuvwxyz
171 assert event.local_time == naive 1nopqrstuvwxyz
172 assert event.optional_time is None 1nopqrstuvwxyz
173 event_id = event.id 1nopqrstuvwxyz
175 with Session(engine) as session: 1nopqrstuvwxyz
176 loaded = session.get(Event, event_id) 1nopqrstuvwxyz
177 assert loaded is not None 1nopqrstuvwxyz
178 assert loaded.occurred_at.tzinfo is UTC 1nopqrstuvwxyz
179 # Query parameters use the same UTC normalization as inserted values.
180 assert ( 1nopqrstuvwxyz
181 session.exec(select(Event).where(Event.occurred_at == aware)).one()
182 is loaded
183 )
184 assert session.exec(select(Event.occurred_at)).one().tzinfo is UTC 1nopqrstuvwxyz
185 later = aware + timedelta(hours=1) 1nopqrstuvwxyz
186 session.exec(update(Event).values(occurred_at=later)) 1nopqrstuvwxyz
187 session.commit() 1nopqrstuvwxyz
188 session.refresh(loaded) 1nopqrstuvwxyz
189 assert loaded.occurred_at == later 1nopqrstuvwxyz
190 assert loaded.occurred_at.tzinfo is UTC 1nopqrstuvwxyz
193class UndefinedOffset(tzinfo): 1abcdefghijklm
194 def utcoffset(self, dt: datetime | None) -> None: 1abcdefghijklm
195 return None 1NOPQRSTUVWXYZ
198@pytest.mark.parametrize("offset", [None, UndefinedOffset()]) 1abcdefghijklm
199@pytest.mark.parametrize("operation", ["insert", "update", "filter"]) 1abcdefghijklm
200def test_naive_database_parameters( 1abcdefghijklm
201 database_engine: Engine, offset: tzinfo | None, operation: str
202) -> None:
203 class Event(SQLModel, table=True): 1NOPQRSTUVWXYZ
204 id: int | None = Field(default=None, primary_key=True) 1NOPQRSTUVWXYZ
205 occurred_at: datetime 1NOPQRSTUVWXYZ
207 naive = datetime(2026, 1, 1, 12, tzinfo=offset) 1NOPQRSTUVWXYZ
208 event = Event.model_validate({"occurred_at": naive}) 1NOPQRSTUVWXYZ
209 # Plain datetime validation remains permissive, including a tzinfo object
210 # that doesn't actually provide an offset.
211 assert event.occurred_at is naive 1NOPQRSTUVWXYZ
212 engine = database_engine 1NOPQRSTUVWXYZ
213 SQLModel.metadata.create_all(engine) 1NOPQRSTUVWXYZ
214 with Session(engine) as session: 1NOPQRSTUVWXYZ
215 with pytest.raises(StatementError, match="timezone information") as exc_info: 1NOPQRSTUVWXYZ
216 if operation == "insert": 1NOPQRSTUVWXYZ
217 session.add(event) 1NOPQRSTUVWXYZ
218 session.flush() 1NOPQRSTUVWXYZ
219 elif operation == "update": 1NOPQRSTUVWXYZ
220 session.exec(update(Event).values(occurred_at=naive)) 1NOPQRSTUVWXYZ
221 else:
222 session.exec(select(Event).where(Event.occurred_at > naive)) 1NOPQRSTUVWXYZ
223 assert isinstance(exc_info.value.orig, ValueError) 1NOPQRSTUVWXYZ
224 assert "NaiveDatetime" in str(exc_info.value.orig) 1NOPQRSTUVWXYZ
227@pytest.mark.parametrize("timezone_enabled", [False, True]) 1abcdefghijklm
228def test_sqlite_explicit_datetime_bypasses_utc_processing( 1abcdefghijklm
229 timezone_enabled: bool,
230) -> None:
231 class Event(SQLModel, table=True): 2cbdbebfbgbhbibjbkblbmbnbob
232 id: int | None = Field(default=None, primary_key=True) 2cbdbebfbgbhbibjbkblbmbnbob
233 occurred_at: datetime = Field(sa_type=DateTime(timezone=timezone_enabled)) 2cbdbebfbgbhbibjbkblbmbnbob
235 aware = datetime(2026, 1, 1, 12, tzinfo=timezone(timedelta(hours=2))) 2cbdbebfbgbhbibjbkblbmbnbob
236 engine = create_engine("sqlite://") 2cbdbebfbgbhbibjbkblbmbnbob
237 SQLModel.metadata.create_all(engine) 2cbdbebfbgbhbibjbkblbmbnbob
238 with Session(engine) as session: 2cbdbebfbgbhbibjbkblbmbnbob
239 event = Event(occurred_at=aware) 2cbdbebfbgbhbibjbkblbmbnbob
240 session.add(event) 2cbdbebfbgbhbibjbkblbmbnbob
241 session.commit() 2cbdbebfbgbhbibjbkblbmbnbob
242 session.refresh(event) 2cbdbebfbgbhbibjbkblbmbnbob
243 assert event.occurred_at == datetime(2026, 1, 1, 12) 2cbdbebfbgbhbibjbkblbmbnbob
244 assert event.occurred_at.tzinfo is None 2cbdbebfbgbhbibjbkblbmbnbob
247def test_postgresql_datetime_processing() -> None: 1abcdefghijklm
248 # PostgreSQL drivers receive aware parameters and may return values in the
249 # connection's timezone. Exercise the complete SQLAlchemy type processors.
250 dialect = postgresql.dialect() 10123456789!#$
251 type_ = UTCDateTime().dialect_impl(dialect) 10123456789!#$
252 bind = type_.bind_processor(dialect) 10123456789!#$
253 result = type_.result_processor(dialect, None) 10123456789!#$
254 assert bind is not None 10123456789!#$
255 assert result is not None 10123456789!#$
256 aware = datetime(2026, 1, 1, 12, tzinfo=timezone(timedelta(hours=2))) 10123456789!#$
257 bound = bind(aware) 10123456789!#$
258 assert bound == aware 10123456789!#$
259 assert bound.tzinfo is UTC 10123456789!#$
260 loaded = result(aware) 10123456789!#$
261 assert loaded == aware 10123456789!#$
262 assert loaded.tzinfo is UTC 10123456789!#$
263 assert bind(None) is None 10123456789!#$
264 assert result(None) is None 10123456789!#$
267@pytest.mark.parametrize( 1abcdefghijklm
268 "operation, reverse",
269 [(add, False), (sub, False), (add, True)],
270)
271def test_postgresql_datetime_interval_arithmetic( 1abcdefghijklm
272 operation: Callable[[Any, Any], Any], reverse: bool
273) -> None:
274 class Event(SQLModel, table=True): 2pbqbrbsbtbubvbwbxbybzbAbBb
275 id: int | None = Field(default=None, primary_key=True) 2pbqbrbsbtbubvbwbxbybzbAbBb
276 occurred_at: datetime 2pbqbrbsbtbubvbwbxbybzbAbBb
278 occurred_at = SQLModel.metadata.tables["event"].c.occurred_at 2pbqbrbsbtbubvbwbxbybzbAbBb
279 duration = timedelta(days=30) 2pbqbrbsbtbubvbwbxbybzbAbBb
280 expression = ( 2pbqbrbsbtbubvbwbxbybzbAbBb
281 operation(duration, occurred_at)
282 if reverse
283 else operation(occurred_at, duration)
284 )
285 # Adding or subtracting a duration still produces a UTC datetime.
286 assert isinstance(expression.type, UTCDateTime) 2pbqbrbsbtbubvbwbxbybzbAbBb
287 # Exercise the actual parameter processor used for database execution.
288 parameter = expression.left if reverse else expression.right 2pbqbrbsbtbubvbwbxbybzbAbBb
289 dialect = postgresql.dialect() 2pbqbrbsbtbubvbwbxbybzbAbBb
290 bind = parameter.type.dialect_impl(dialect).bind_processor(dialect) 2pbqbrbsbtbubvbwbxbybzbAbBb
291 bound = bind(parameter.value) if bind is not None else parameter.value 2pbqbrbsbtbubvbwbxbybzbAbBb
292 assert bound == duration 2pbqbrbsbtbubvbwbxbybzbAbBb
295def test_existing_sqlite_utc_data() -> None: 1abcdefghijklm
296 class Event(SQLModel, table=True): 1%'()*+,-./:;=
297 id: int | None = Field(default=None, primary_key=True) 1%'()*+,-./:;=
298 occurred_at: datetime 1%'()*+,-./:;=
300 engine = create_engine("sqlite://") 1%'()*+,-./:;=
301 with engine.begin() as connection: 1%'()*+,-./:;=
302 connection.exec_driver_sql( 1%'()*+,-./:;=
303 "CREATE TABLE event (id INTEGER NOT NULL PRIMARY KEY, "
304 "occurred_at DATETIME NOT NULL)"
305 )
306 connection.exec_driver_sql( 1%'()*+,-./:;=
307 "INSERT INTO event VALUES (1, '2026-01-01 12:00:00.000000')"
308 )
309 context = MigrationContext.configure(connection, opts={"compare_type": True}) 1%'()*+,-./:;=
310 assert compare_metadata(context, SQLModel.metadata) == [] 1%'()*+,-./:;=
312 with Session(engine) as session: 1%'()*+,-./:;=
313 event = session.get(Event, 1) 1%'()*+,-./:;=
314 assert event is not None 1%'()*+,-./:;=
315 assert event.occurred_at == datetime(2026, 1, 1, 12, tzinfo=UTC) 1%'()*+,-./:;=
316 assert event.occurred_at.tzinfo is UTC 1%'()*+,-./:;=
319def test_alembic_datetime_rendering() -> None: 1abcdefghijklm
320 class Event(SQLModel, table=True): 2? @ [ ] ^ _ ` { | } ~ abbb
321 id: int | None = Field(default=None, primary_key=True) 2? @ [ ] ^ _ ` { | } ~ abbb
322 occurred_at: datetime 2? @ [ ] ^ _ ` { | } ~ abbb
324 table = SQLModel.metadata.tables["event"] 2? @ [ ] ^ _ ` { | } ~ abbb
325 migration = ops.UpgradeOps(ops=[ops.CreateTableOp.from_table(table)]) 2? @ [ ] ^ _ ` { | } ~ abbb
326 rendered = render_python_code(migration) 2? @ [ ] ^ _ ` { | } ~ abbb
327 assert "sqlmodel.sql.sqltypes.UTCDateTime()" in rendered 2? @ [ ] ^ _ ` { | } ~ abbb
328 # Execute the generated migration, including the custom type constructor.
329 namespace: dict[str, Any] = {} 2? @ [ ] ^ _ ` { | } ~ abbb
330 exec( 2? @ [ ] ^ _ ` { | } ~ abbb
331 "import sqlalchemy as sa\nimport sqlmodel.sql.sqltypes\n"
332 "from alembic import op\ndef upgrade():\n " + rendered,
333 namespace,
334 )
335 output = StringIO() 2? @ [ ] ^ _ ` { | } ~ abbb
336 context = MigrationContext.configure( 2? @ [ ] ^ _ ` { | } ~ abbb
337 dialect_name="postgresql",
338 opts={"as_sql": True, "output_buffer": output},
339 )
340 with Operations.context(context): 2? @ [ ] ^ _ ` { | } ~ abbb
341 namespace["upgrade"]() 2? @ [ ] ^ _ ` { | } ~ abbb
342 assert "occurred_at TIMESTAMP WITH TIME ZONE NOT NULL" in output.getvalue() 2? @ [ ] ^ _ ` { | } ~ abbb