Opened 19 years ago

Closed 19 years ago

Last modified 17 years ago

#1148 closed defect (fixed)

None

Reported by: hugo Owned by: Adrian Holovaty
Component: Core (Mail) Version: other branch
Severity: normal Keywords: None
Cc: None Triage Stage: Unreviewed
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

When using the get_DATEFIELD_list function with the 'day' kind, the last day isn't seen:

pages.get_creation_date_list('day', creation_date__year=2005, creation_date__month=12)

produces the following SQL:

>>> db.db.queries
[{'time': '0.146', 'sql': 'SELECT DATE_TRUNC(\'day\', "cms_pages"."creation_date")  FROM "cms_pages" 
WHERE "cms_pages"."creation_date" BETWEEN 2005-01-01 AND 2005-12-31 AND EXTRACT(\'month\' FROM 
"cms_pages"."creation_date") = 12 GROUP BY 1 ORDER BY 1'}]

But this produces the following results:

rfc1437=> select date_trunc('day', creation_date), count(*) 
          from cms_pages 
          where creation_date between date'2005-12-01' and date'2005-12-31' 
          group by date_trunc('day', creation_date) 
          order by date_trunc('day', creation_date);
       date_trunc       | count 
------------------------+-------
 2005-12-01 00:00:00+01 |     2
 2005-12-02 00:00:00+01 |     2
 2005-12-03 00:00:00+01 |     1
 2005-12-04 00:00:00+01 |     3
 2005-12-05 00:00:00+01 |     3
 2005-12-06 00:00:00+01 |     1
 2005-12-07 00:00:00+01 |     3
 2005-12-08 00:00:00+01 |     2
 2005-12-09 00:00:00+01 |     8
 2005-12-10 00:00:00+01 |     2
 2005-12-11 00:00:00+01 |     1
 2005-12-12 00:00:00+01 |     3
 2005-12-13 00:00:00+01 |     2
 2005-12-14 00:00:00+01 |     1
 2005-12-15 00:00:00+01 |     2
 2005-12-16 00:00:00+01 |     2
 2005-12-17 00:00:00+01 |     4
 2005-12-18 00:00:00+01 |     5
 2005-12-19 00:00:00+01 |     1
 2005-12-20 00:00:00+01 |     2
 2005-12-21 00:00:00+01 |     2
 2005-12-22 00:00:00+01 |     3
 2005-12-23 00:00:00+01 |     2
 2005-12-26 00:00:00+01 |     4
 2005-12-27 00:00:00+01 |     2
 2005-12-28 00:00:00+01 |     1
 2005-12-29 00:00:00+01 |     2
 2005-12-30 00:00:00+01 |     1

But I do have pages for 31.12.2005:

rfc1437=> select count(*) from cms_pages 
          where date_trunc('day', creation_date) = date'2005-12-31';
 count 
-------
     2
(1 row)

Looks like PostgreSQL between is excluding the right border, so it should use between with the last day plus one day. Or just use extract with 'year' and 'month' and explicit equality to the given year and month instead of the between for the year. A quite annoying bug that might even go unnotice in most cases - I only noticed it, because I use the get_DATELIST_list function to produce a calendar view and noticed that the 31st of december is missing in the calendar ...

Attachments (2)

year.diff (1.6 KB ) - added by hugo 19 years ago.
patch to change year to same behaviour as month
year-alternative.diff (591 bytes ) - added by hugo 19 years ago.
alternative patch for same problem

Download all attachments as: .zip

Change History (9)

by hugo, 19 years ago

Attachment: year.diff added

patch to change year to same behaviour as month

comment:1 by hugo, 19 years ago

Summary: get_datefield_list doesn't fetch the last day[patch] fix get_datefield_list year handling

by hugo, 19 years ago

Attachment: year-alternative.diff added

alternative patch for same problem

comment:2 by hugo, 19 years ago

I attached two patches - the first one changes the 'year' behaviour to the same behaviour as 'month', the second one keeps the different 'year' behaviour and just fixes the last-day problem. The first one might be a bit slower, as some databases might optimize date-between queries better than extract queries, while the second one might be incorrect if some databases expect between to _include_ the right boundary (while PostgreSQL seems to _exclude_ the right boundary).

Can't say which one would be better, both seem to work fine on my site, though.

comment:3 by Esaj, 19 years ago

Is this related to #992 at all?

comment:4 by hugo, 19 years ago

No, that's a last-day-of-month problem, this is a last-day-of-year problem :-)

The main problem is that we select against days, but internally it's a timestamp and so has a time, too - so the between will only go until 00:00 of the given day, but posts usually are somewhere later than 00:00 and so are left out. So my assumption that PostgreSQL excludes the right border is wrong - it's just that the query has a far too early right border.

comment:5 by Adrian Holovaty, 19 years ago

Resolution: fixed
Status: newclosed

(In [2338]) Fixed #1148 -- Fixed off-by-one error for some databases in get_DATEFIELD_list() at the edge of a year. Thanks, Hugo

comment:6 by James Bennett, 18 years ago

(In [4270]) 0.90-bugfixes: backport [2238]. Refs #1148

comment:7 by anonymous, 17 years ago

Cc: None added
Component: Core frameworkdjango.core.mail
Keywords: None added
Summary: [patch] fix get_datefield_list year handlingNone
Version: other branch
Note: See TracTickets for help on using tickets.
Back to Top