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

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

6 

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) 

25 

26 

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

50 

51 class Event(SQLModel, table=True): 1ABCDEFGHIJKLM

52 id: int | None = Field(default=None, primary_key=True) 1ABCDEFGHIJKLM

53 occurred_at: annotation 1ABCDEFGHIJKLM

54 

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

59 

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

73 

74 

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

78 

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

81 

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

85 

86 

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

98 

99 class Event(SQLModel, table=True): 2PbQbRbSbTbUbVbWbXbYbZb0b1b

100 id: int | None = Field(default=None, primary_key=True) 2PbQbRbSbTbUbVbWbXbYbZb0b1b

101 occurred_at: annotation = field 2PbQbRbSbTbUbVbWbXbYbZb0b1b

102 

103 assert SQLModel.metadata.tables["event"].c.occurred_at.type is sa_type 2PbQbRbSbTbUbVbWbXbYbZb0b1b

104 

105 

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

110 

111 assert SQLModel.metadata.tables["event"].c.occurred_at.type.timezone is False 2[b]b^b_b`b{b|b}b~bacbcccdc

112 

113 

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

120 

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

125 

126 

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

144 

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

148 

149 

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

156 

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

174 

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

191 

192 

193class UndefinedOffset(tzinfo): 1abcdefghijklm

194 def utcoffset(self, dt: datetime | None) -> None: 1abcdefghijklm

195 return None 1NOPQRSTUVWXYZ

196 

197 

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

206 

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

225 

226 

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

234 

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

245 

246 

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!#$

265 

266 

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

277 

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

293 

294 

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%'()*+,-./:;=

299 

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%'()*+,-./:;=

311 

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%'()*+,-./:;=

317 

318 

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

323 

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