[Bug 42868] New: Bookings are storing incorrect timezone values
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42868 Bug ID: 42868 Summary: Bookings are storing incorrect timezone values Initiative type: --- Sponsorship --- status: Product: Koha Version: Main Hardware: All OS: All Status: NEW Severity: major Priority: P5 - low Component: Staff interface Assignee: koha-bugs@lists.koha-community.org Reporter: nick@bywatersolutions.com QA Contact: testopia@bugs.koha-community.org CC: gmcharlt@gmail.com Target Milestone: --- Somehow, between the date picker and the DB, the bookings module is converting dates incorrectly and setting the wrong dates. To recreate: 1 - Edit koha-conf.xml 2 - Set timezone: <timezone>America/New_York</timezone> 3 - In Koha, enable bookings syspref 4 - Find a record, click the items tab, make an item bookable 5 - Place new booking, choose a date range 6 - View the bookings tab - note the booking extends into the date before 7- Check the DB: MariaDB [koha_kohadev]> select * from bookings WHERE booking_id=2; +------------+-----------+-----------+---------+-------------------+---------------------+---------------------+---------------------+---------------------+--------+---------------------+ | booking_id | patron_id | biblio_id | item_id | pickup_library_id | start_date | end_date | creation_date | modification_date | status | cancellation_reason | +------------+-----------+-----------+---------+-------------------+---------------------+---------------------+---------------------+---------------------+--------+---------------------+ | 2 | 12 | 437 | 971 | FPL | 2026-06-23 20:00:00 | 2026-06-26 19:59:59 | 2026-06-17 15:00:40 | 2026-06-17 15:00:40 | new | NULL | +------------+-----------+-----------+---------+-------------------+---------------------+---------------------+---------------------+---------------------+--------+---------------------+ 1 row in set (0.000 sec) Start date time is 20:00 - really 00:00 minus 4 - New York is UTC minus 4 - it seems we shouldn't subtract more -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42868 Nick Clemens (kidclamp) <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|koha-bugs@lists.koha-commun |martin.renvoize@openfifth.c |ity.org |o.uk Depends on| |37707 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37707 [Bug 37707] Lead/Trail times should work in combination -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42868 Daniel B. <dbarden@cuyahogalibrary.org> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |dbarden@cuyahogalibrary.org -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42868 Danielle M. <dmeininger591@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |dmeininger591@gmail.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42868 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |lucas@bywatersolutions.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42868 --- Comment #1 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- I think because in place_booking.js with startDate and endDate we use utc(startDate) and utc(endDate) Instead we can use https://day.js.org/docs/en/plugin/timezone since we already load it: [% Asset.js("lib/dayjs/plugin/timezone.js") | $raw %] -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42868 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42868 --- Comment #2 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- Created attachment 200924 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=200924&action=edit Bug 42868: Use local timezone instead of UTC when calculating start and end dates To test: 1 - Edit koha-conf.xml 2 - Set timezone: <timezone>America/New_York</timezone> 3 - In Koha, enable bookings syspref 4 - Find a record, click the items tab, make an item bookable 5 - Place new booking, choose a date range 6 - View the bookings tab - note the booking extends into the date before 7- Check the DB, the start_date and end_date are wrong 8. APPLY PATCH, restart_all, clear browsewr cache 9. Place a new booking. 10. MariaDB [koha_kohadev]> select * from bookings WHERE booking_id={new booking} 11. Notice the start time should be {start_date} 00:00:00 and the end time should be {end_date} 23:59:59 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42868 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |martin.renvoize@openfifth.c | |o.uk Assignee|martin.renvoize@openfifth.c |lucas@bywatersolutions.com |o.uk | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42868 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42868 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #200924|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42868 --- Comment #3 from David Nind <david@davidnind.com> --- Created attachment 200933 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=200933&action=edit Bug 42868: Use local timezone instead of UTC when calculating start and end dates To test: 1 - Edit koha-conf.xml 2 - Set timezone: <timezone>America/New_York</timezone> 3 - In Koha, enable bookings syspref 4 - Find a record, click the items tab, make an item bookable 5 - Place new booking, choose a date range 6 - View the bookings tab - note the booking extends into the date before 7- Check the DB, the start_date and end_date are wrong 8. APPLY PATCH, restart_all, clear browsewr cache 9. Place a new booking. 10. MariaDB [koha_kohadev]> select * from bookings WHERE booking_id={new booking} 11. Notice the start time should be {start_date} 00:00:00 and the end time should be {end_date} 23:59:59 Signed-off-by: David Nind <david@davidnind.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42868 Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- QA Contact|testopia@bugs.koha-communit |martin.renvoize@openfifth.c |y.org |o.uk -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42868 Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Sponsorship status|--- |Unsponsored Status|Signed Off |Passed QA Patch complexity|--- |Small patch -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42868 Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #200933|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42868 --- Comment #4 from Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> --- Created attachment 201360 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=201360&action=edit Bug 42868: Use local timezone instead of UTC when calculating start and end dates To test: 1 - Edit koha-conf.xml 2 - Set timezone: <timezone>America/New_York</timezone> 3 - In Koha, enable bookings syspref 4 - Find a record, click the items tab, make an item bookable 5 - Place new booking, choose a date range 6 - View the bookings tab - note the booking extends into the date before 7- Check the DB, the start_date and end_date are wrong 8. APPLY PATCH, restart_all, clear browsewr cache 9. Place a new booking. 10. MariaDB [koha_kohadev]> select * from bookings WHERE booking_id={new booking} 11. Notice the start time should be {start_date} 00:00:00 and the end time should be {end_date} 23:59:59 Signed-off-by: David Nind <david@davidnind.com> Signed-off-by: Martin Renvoize <martin.renvoize@openfifth.co.uk> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42868 --- Comment #5 from Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> --- Created attachment 201361 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=201361&action=edit Bug 42868: (follow-up) Fix stale comment referencing removed UTC boundary logic The comment above the day-boundary calculation still described the previous dayjs.utc() approach, which was replaced by dayjs.tz() in the parent commit. Update it to describe the current behaviour so it doesn't mislead future maintainers into re-introducing the timezone shift bug this patch fixed. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42868 --- Comment #6 from Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> --- Created attachment 201362 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=201362&action=edit Bug 42868: (follow-up) Add regression test for non-UTC library timezone The bug this fixed only manifests when the library's configured timezone (C4::Context->tz) is not UTC: Koha::Object::_recursive_fixup converts an incoming RFC3339 instant to that timezone before storing it in the timezone-naive 'bookings.start_date'/'end_date' columns, so a UTC-anchored boundary shifts by the timezone offset on write. None of the existing Cypress specs catch this, because they never run with the browser/test-runner timezone diverging from the configured library timezone - both always come from the same container clock. Add a Perl-level API test that mocks C4::Context->tz to a non-UTC zone (America/New_York) and asserts the boundaries sent by place_booking.js round-trip to the correct library-local date, pinning this regression independently of any particular environment's timezone. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42868 --- Comment #7 from Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> --- Thanks for stepping in here Lucas.. my brain was shot in terms of timezone handling and it looks like I was going too deep with the history. Anywho.. this looks good and works in all my testing. Passing QA -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42868 Kristi Krueger <kkrueger@cuyahogalibrary.org> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |kkrueger@cuyahogalibrary.or | |g --- Comment #8 from Kristi Krueger <kkrueger@cuyahogalibrary.org> --- Thank you! Any chance for a backport on this? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42868 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords| |rel_25_11_candidate, | |rel_26_05_candidate -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42868 Pedro Amorim (ammopt) <pedro.amorim@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)| |26.11.00 released in| | Status|Passed QA |Pushed to main -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42868 --- Comment #9 from Pedro Amorim (ammopt) <pedro.amorim@openfifth.co.uk> --- Thanks everyone! Pushed to main for 26.11! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42868 Paul Derscheid <paul.derscheid@lmscloud.de> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=43108 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42868 --- Comment #10 from Kristi Krueger <kkrueger@cuyahogalibrary.org> --- Glad to see this is pushed to main! Hoping for a backport on this fix. Can it be added as a candidate? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42868 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)|26.11.00 |26.11.00,26.05.03 released in| | Status|Pushed to main |Pushed to stable -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42868 --- Comment #11 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- Nice work everyone! Pushed to 26.05.x for the upcoming 26.05.03 release. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42868 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords|rel_26_05_candidate | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42868 Bug 42868 depends on bug 37707, which changed state. Bug 37707 Summary: Lead/Trail times should work in combination https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37707 What |Removed |Added ---------------------------------------------------------------------------- Status|Needs documenting |RESOLVED Resolution|--- |FIXED -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42868 --- Comment #12 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- Created attachment 208763 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=208763&action=edit Bug 42868: Convert dates to localtime before they go into a flatpickr I asked Claude to help me identify all the places where we need the conversion before it goes into a date picker Assisted-by: Claude <noreply@anthropic.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42868 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #208763|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42868 --- Comment #13 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- attached to wrong bug, oops -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org