id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
1148	None	hugo	Adrian Holovaty	"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 ...

"	defect	closed	Core (Mail)	other branch	normal	fixed	None	None	Unreviewed	1	0	0	0	0	0