Opened 10 years ago

Closed 10 years ago

Last modified 8 years ago

#1148 closed defect (fixed)

None

Reported by: hugo Owned by: adrian
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: UI/UX:

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 10 years ago.
patch to change year to same behaviour as month
year-alternative.diff (591 bytes) - added by hugo 10 years ago.
alternative patch for same problem

Download all attachments as: .zip

Change History (9)

Changed 10 years ago by hugo

patch to change year to same behaviour as month

comment:1 Changed 10 years ago by hugo

  • Summary changed from get_datefield_list doesn't fetch the last day to [patch] fix get_datefield_list year handling

Changed 10 years ago by hugo

alternative patch for same problem

comment:2 Changed 10 years ago by hugo

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 Changed 10 years ago by Esaj

Is this related to #992 at all?

comment:4 Changed 10 years ago by hugo

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 Changed 10 years ago by adrian

  • Resolution set to fixed
  • Status changed from new to closed

(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 Changed 9 years ago by ubernostrum

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

comment:7 Changed 8 years ago by anonymous

  • Cc None added
  • Component changed from Core framework to django.core.mail
  • Keywords None added
  • Summary changed from [patch] fix get_datefield_list year handling to None
  • Version set to other branch
Note: See TracTickets for help on using tickets.
Back to Top