None

[hugo]

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 ...

[hugo]

patch to change year to same behaviour as month

[hugo]

alternative patch for same problem

[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.

[Esaj]

Is this related to #992 at all?

[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.

[Adrian Holovaty]

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

[James Bennett]

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

[anonymous]

Photograph Poetry