Forum .LRN Q&A: Fix for bug on daily calendar displays (postgres)

There's a special case bug on daily calendar displays. Special case because it only affects entries who's start time falls on midnight. In the query calendar::one_day_display.select_day_items located in /packages/calendar/tcl/calendar-display-procs-postgresql.xql, there's a chunk that reads:

and start_date between
    to_date(:current_date,:date_format) and
    to_date(:current_date,:date_format) + (24 - 1/3600)/24

This will cause items who's start date is exactly midnight to be displayed on the day they are defined as well as 1 day before. This is because the computation "to_date(:current_date,:date_format) + (24 - 1/3600)/24" will return a date exactly 1 day at midnight after $current_date, thus making events the next day (midnight) included in the query.

My temporary fix is to change it to:

and start_date >= to_date(:current_date,:date_format)
and start_date <  to_date(:current_date,:date_format) + 1

We replace the computation with + 1 as it behaves the same way and we avoid the math overhead.

This can also be seen on the the query calendar::list_display.select_list_items on the same file and I changed it there too.

The bug doesn't affect Oracle because in Oracle, the computation will return a date 1 second less than next day's midnight.