due dates rolled past midnight in local time zone

[James Fournie]

Evergreen: 2.2
Postgres: 9.1

When checking out items, any items with an interval > 1 day will be rolled to midnight server time. However, if a library is a time zone east of your server, the item will be due at 1AM the next day, thus patrons will see the wrong due date.

I've created a patch which adds a timezone org unit setting and actually sets the time zone correctly in the circulation perl code

HOWEVER, there is still another issue -- a database trigger still rolls the time to midnight server time.

Simply removing this trigger will alter the behaviour of the manual due-date selection widget. Currently this widget will roll the due dates to midnight for >= 1 day circ durations. Current users may rely on or expect this behaviour. Simply removing the database trigger will cause that widget to always literally use the exact time value specified.

I would suggest that the widget is not ideal UX in the first place and recommend that prior to removing the trigger we re-work the widget to have several options:

1) Select a specific due date
2) Select a specific due date /w time
3) Select a relative due date (ie: 5 days, 1 hour, etc)

For now, the first part of this fix is available here and it adds YAOUS for timezone. In order to have a list of timezones for the setting, this patch also creates a list of every time zone in a table called "config.timezone" populated from pg_timezone_names. These time zones also accomodate jurisdictions which don't participate in daylight savings time, for example America/Dawson_Creek is MST but never changes for DST. The full list is quite large, for simplicity it is suggested that Evergreen sites could remove time zones you don't need.

working/user/jamesrf/timezone-patch

[Dan Wells (dbw2)]

As an alternative to throwing out the trigger, changing the create_due_date() function, and redoing the due date UI, could we just pull the new time zone info into the trigger instead? Use 'SET LOCAL TIME ZONE XYZ' (substitute a real TZ) in the trigger before we mangle the date, then it would get set to 11:59pm for the library doing the circ, and whatever that happens to be for the server.

We might need to consider in a few places what happens to the date when it comes back out, but if we already see the "wrong" due date now, then the code is already doing the time zone shift on output in at least one place.

[Robert J Jackson (rjackson-deactivatedaccount)]

Library reported issue with setting specific Due Date where the time is always set to 11:59 PM regardless of the time set on the checkout display. I have verified locally this issue by performing a specific due date checkout and setting the time to 2 hours in advance of current time. In this case wall clock time was 2012-12-03 08:07:13-05 and due date ended up being set to 2012-12-03 23:59:00-05

Here is the action.circulation record for comparison:

-[ RECORD 6 ]-------+------------------------------
id | 30331008
usr | 1876933
xact_start | 2012-12-03 08:07:13-05
xact_finish | 2012-12-03 08:07:43.547725-05
unrecovered |
target_copy | 29657877
circ_lib | 73
circ_staff | 1
checkin_staff | 1
checkin_lib | 73
renewal_remaining | 1
due_date | 2012-12-03 23:59:00-05
stop_fines_time | 2012-12-03 08:07:43.547725-05
checkin_time | 2012-12-03 08:07:43.547725-05
duration | 7 days
fine_interval | 1 day
recurring_fine | 0.00
max_fine | 0.00
phone_renewal | f
desk_renewal | f
opac_renewal | f
duration_rule | 1_7_3_1
recurring_fine_rule | no_fines
max_fine_rule | zero
stop_fines | CHECKIN
create_time | 2012-12-03 08:07:13-05
workstation | 10263
parent_circ |
checkin_workstation | 10263
checkin_scan_time | 2012-12-03 08:07:43.547725-05
grace_period | 1 day
copy_location | 24682

[James Fournie (jfournie)]

Hi Dan --

I respectfully disagree, the UI should probably be changed. We should NEVER offer a time picker to a user if the time is going to automatically picked for you anyway half the time. I understand that some users may expect that behaviour after using it for so long and understand the conditions when it happens, but that doesn't really make any sense at all from a usability perspective when we can simply make a better widget that is intuitive. You can see that rjackson reported this as a problem at his library, it's a usability problem.

Likewise, I don't like the idea of a trigger since you're again changing a user's expectation, only this time a more technical user. If I'm inserting a circ directly using cstore or directly into the database, I should reasonably assume that the due time isn't going to be changed from what I specify. If it causes problems, if I'm using cstore calls I should know that I could break things. I think the rolling of 23:59 is purely application code.

Anyway my 2c on that.

So far I haven't yet seen the wrong date come out of any Javascript-based interfaces, but it's possible somewhere may be broken. I haven't yet investigated the reporter or TPAC or Action Triggers, the latter two may be problematic.

[Andrea Neiman (aneiman)]

Marking as duplicate since Mike's work in bug 1705524 will address these issues