Opened 4 years ago

Closed 4 years ago

#25487 closed New feature (wontfix)

Add support for months and years in INTERVAL field of PostgreSQL

Reported by: None Owned by: None
Component: contrib.postgres Version: master
Severity: Normal Keywords: postgres, interval
Cc: Marc Tamlyn Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

PostgeSQL supports months and years in INTERVAL field, but django doesn't support it.
I propose add new field postgres.IntervalField that supports months and years by using python-dateutil python library.

Change History (4)

comment:1 Changed 4 years ago by Tim Graham

Cc: Marc Tamlyn added
Summary: Add support months and years in INTERVAL field of PosrgreSQLAdd support for months and years in INTERVAL field of PostgreSQL

What's the reason DurationField doesn't support it? How soon do we overflow the bigint storage of other database backends?

comment:2 Changed 4 years ago by None

I propose add special for PosgreSQL field, that will be using INTERVAL full.

Month is not a static value and you can't convert it to bigint.
For example:

TIMESTAMP '2001-02-02' + INTERVAL '1 month' = TIMESTAMP '2001-03-02'
TIMESTAMP '2001-02-02' + INTERVAL '31' days' = TIMESTAMP '2001-03-05'

In python you must to use python-dateutil:

>>> datetime(2015, 1, 31) + relativedelta(months=1)
datetime.datetime(2015, 2, 28, 0, 0)
>>> datetime(2012, 2, 29) + relativedelta(months=12)
datetime.datetime(2013, 2, 28, 0, 0)
Last edited 4 years ago by None (previous) (diff)

comment:3 Changed 4 years ago by Marc Tamlyn

Support for non-fixed durations is complicated - As liminspace mentioned Python's timedelta does not support months or years (see https://docs.python.org/3.2/library/datetime.html#datetime.timedelta). Oracle actually has two completely separate fields for them, and the other databases we could not store it in a single column either.

Also, what is the form field representation? How do we anticipate a user will specify "2 months, 4 days and 1 hour" (in every language?), and will they understand that 30 days != 1 month? In fact in postgres depending on which functions you use to calculate age, you either get calendar months or 30 day "months".

I think this should be possible as an external field for postgres and/or Oracle, as it has external python dependencies as well it should live as an external package. I'm not sure what psycopg2 does for month/year based durations as normally it will try to cast interval to a timedelta.

comment:4 Changed 4 years ago by Tim Graham

Resolution: wontfix
Status: newclosed
Note: See TracTickets for help on using tickets.
Back to Top