CODEWITHSUNDEEP
pythonsqlsqlalchemypsycopg2
Roman
Roman

Reputation: 3941

Sqlalchemy dialects DATERANGE vs. psycopg2.extras.DateRange changing bounds

I need to change the bounds for my DATERANGE column. The default is bounds='[)'.

This is my current column:

from sqlalchemy.dialects.postgresql import DATERANGE
booked_date = Column(DATERANGE(), nullable=False)

The problem is I cant do anything with the DATERANGE() in my example, it doesnt have any arguments and there is no documentation on this. I always thought that this DATERANGE() comes from psycopg2.extras.DateRange, but it seems its not the case, because according to the docs DateRange should accept these arguments:

class psycopg2.extras.DateRange(lower=None, upper=None, bounds='[)', empty=False)

How do I change the bounds on my example?

Where is the difference between psycopg2.extras.DateRange and sqlalchemy.dialects.postgresql import DATERANGE?

EDIT

My column remains as is. But I need to add daterange datatypes to the db by using DateRange from psycopg2:

un_daterange = DateRange(undate_lower.date(), undate_upper.date(), bounds="[]")

new_booking = UserBooksRoom(booked_date=un_daterange, date_added=datetime.today(),
booking_type='correction')

The problem here is if I select 21.01.2019 - 27.01.2019 un_daterange will look like this: DateRange(datetime.date(2019, 1, 21), datetime.date(2019, 1, 27), '[]'), which is correct, but in the DB it looks like this: [2019-01-21,2019-01-28). The upper bound is 1 day higher, why? I thoguht '[]' means include lower and upper bound.

Also while checking wether the booking is available:

I select the date 19.01.2019 - 21.01.2019, it will correctly tell me that this date is not available because 21.01.2019 is the lower bound.

If I select 27.01.2019 - 30.01.2019 it will also be correct.

If I select 28.01.2019 - 30.01.2019 it will tell the date is available.

So all in all It WORKS correctly, date) simply means date - 1 day. But why is it this complicated? And why bounds="[]" does not work?

EDIT

I checked again and bounds does work. Without bound="[]" it would look like this in the DB: [2019-01-21,2019-01-27) and selecting 27.01.2019 - 30.01.2019 would tell me that it is possible and that would be wrong.

Basically when displaying the range information to a user with .lower and .upper, you need to subtract 1 day from the upper bound.

Upvotes: 2

Views: 1683

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52949

The difference is that

  • psycopg2.extras.DateRange represents a Postgresql daterange value. You define bounds on a per value basis.
  • sqlalchemy.dialects.postgresql.DATERANGE is from SQLAlchemy, as should be clear from the qualified name, and represents a Postgresql daterange type, as used in a CREATE TABLE statement etc. It does not care about the bounds of the values you store in a column having this type.

To recap: use DATERANGE when creating models and Tables in SQLAlchemy to define the type of a column, use DateRange to represent values that can be stored in such a column, if using Psycopg2 as the DB-API driver.

Upvotes: 3

Related Questions