Repository navigation
How can I make DateTimeTZRange work? #235
Description
Activity
I experienced no problems saving instances with the
conlistversion of your code. How did you initialize your model? This, for example, worked for me:MyModel(time_range=[datetime.now(timezone.utc), datetime.max])Which version of PostgreSQL are you using?
This is a bit tricky due to Pydantic need validators etc. Basically the DateTimeTZRange type does not have the required method to convert its data to a dict, so you need to do that manually.
- The code I made below adds to Pydantic's ENCODERS_BY_TYPE
- It then creates a sub-class of DateTimeTZRange to include required validators
- This subclass also modifies the schema for presentation in the api docs correctly (with example)
- Because you are sub-classing the type you don't need
arbitrary_types_allowed = Trueor anyclass Config:
from psycopg2.extras import DateTimeTZRange as DateTimeTZRangeBase from sqlalchemy.dialects.postgresql import TSTZRANGE from sqlmodel import ( Column, Field, Identity, SQLModel, ) from pydantic.json import ENCODERS_BY_TYPE ENCODERS_BY_TYPE |= {DateTimeTZRangeBase: str} class DateTimeTZRange(DateTimeTZRangeBase): @classmethod def __get_validators__(cls): yield cls.validate @classmethod def validate(cls, v): if isinstance(v, str): lower = v.split(", ")[0][1:].strip().strip() upper = v.split(", ")[1][:-1].strip().strip() bounds = v[:1] + v[-1:] return DateTimeTZRange(lower, upper, bounds) elif isinstance(v, DateTimeTZRangeBase): return v raise TypeError("Type must be string or DateTimeTZRange") @classmethod def __modify_schema__(cls, field_schema): field_schema.update(type="string", example="[2022,01,01, 2022,02,02)") class EventBase(SQLModel): __tablename__ = "event" timestamp_range: DateTimeTZRange = Field( sa_column=Column( TSTZRANGE(), nullable=False, ), ) class Event(EventBase, table=True): id: int | None = Field( default=None, sa_column_args=(Identity(always=True),), primary_key=True, nullable=False, )
Pydantic issues:
- What is the correct way to define custom pydantic types that also play nicely with JSON Schema? fastapi#1285
- Customizing both decoding and encoding of a type pydantic/pydantic#951
- Support Generic Container Types pydantic/pydantic#380
Pydantic reading:
Reacted by Jonas MantheyReacted by ZafferWe had a similar question for our project, but for a
DateRangeadapter instead.We adapted @Zaffer's suggestion, and it worked splendidly for persisting a value. But when the instance was refreshed/reloaded from the db, the field's resulting value on the
SQLModel-derived instance was not the expected customDateRangesubclass, but rather asqlalchemy.dialects.postgresql.ranges.Rangeinstance (i.e., an instance of theDATERANGEused to specify theColumntype).I'm still trying to understand the mechanisms used to hydrate a row of SQL data into a Pydantic instance, but I would've expected it would attempt to cover the loaded data into the specified type on the
SQLModelclass.Is this a bug, a misconfiguration, or a misunderstanding of how SQLModel/Pydantic/SQLAlchemy work?
from datetime import date from psycopg2.extras import DateRange as DateRangeBase from sqlalchemy.dialects.postgresql import DATERANGE def _to_date(value: str) -> date: vals = map( int, value.strip().replace("[", "").replace(")", "").split("-", maxsplit=2) ) return date(*vals) class DateRange(DateRangeBase): """ A range of dates, used for persisting in a PostgreSQL database. """ @classmethod def __get_validators__(cls): yield cls.validate @classmethod def validate(cls, v): if isinstance(v, DateRangeBase): return v if isinstance(v, str): lower, upper = map(_to_date, v.split(sep=",", maxsplit=1)) bounds = v[:1] + v[-1:] return DateRange(lower, upper, bounds) raise TypeError("Type must be a string or DateRange") @classmethod def __modify_schema__(cls, field_schema): field_schema.update(type="string", example="[2022-01-01,2022-02-02)") class EventBase(SQLModel): date_range: DateRange = Field(sa_column=Column(DATERANGE(), nullable=False))
@shaug - did you ever get any further with this as a solution?
We've ended up needing to create SQLAlchemy TypeDecorators for every type we wanted to properly convert from its database representation into its Python instance.
Reacted by Troublador, Soumendra Kumar Sahoo and Yurii Motov- locked and limited conversation to collaborators
on May 18, 2026
First Check
Commit to Help
Example Code
Description
I have a model that has a DateTimeTZRange field. When I try to save an instance of this model, I get the following error:
When I use
conlistrather thanDateTimeTZRange, I don't get errors but the new objects always haveNonein theirtime_range. I couldn't find any documentation on how to use custom PostgreSQL fields in SQLModel, and the documentation I found on sqlalchemy doesn't seem to work smoothly on SQLModel.Operating System
macOS
Operating System Details
No response
SQLModel Version
0.0.4
Python Version
3.9.9
Additional Context
No response