http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=10824 --- Comment #1 from David Cook <dcook@prosentient.com.au> --- Created attachment 20791 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=20791&action=edit Bug 10824 - OAI-PMH repository/server not handling time in "from" argument This patch remotes the DATE() function from around timestamp, and adds a sub that strips the UTC designators "T" and "Z" from incoming "from" and "until" arguments in OAI-PMH requests so that they're more compliant with MySQL (and probably other databases as well). TEST PLAN: 0) Note down a selection of timestamps from your biblio table 1) Enable your OAI-PMH server through the global system preferences Web services tab. 2) Craft and submit a similar request to the following in your browser: KOHAINSTANCE/cgi-bin/koha/oai.pl?verb=ListRecords&metadataPrefix=oai_dc& from=2013-09-02T13:44:33Z&until=2013-09-05T13:44:33Z Change the exact dates to accord with your timestamps, but keep the YYYY-MM-DDTHH:MM:SSZ format. 3) Note the unexpected behaviour. A "from" argument with the timestamp 2013-09-02T13:44:33Z will show records from 2013-09-03 but not records from 2013-09-02 even though the timestamp in the database will say "2013-09-02 13:44:33". 4) APPLY THE PATCH 5) Resubmit the links you tried above 6) Note that the applicable records now appear! -- Developer Note: We could've not stripped the UTC designators and used DATE() around the parameters in the SQL queries, but that would have lost the whole purpose of using times in the "from" arguments, since they would've been generalized to just the dates. I think this is probably the best solution. Admittedly, creating "form_arg" and "until_arg" hashrefs in the ResumptionToken object might not be ideal, but I preferred that to copying the _strip_UTC_designator subroutine into two other objects. Perhaps this sub could go somewhere else and be imported into those other two objects but this seemed to be the most sensible decision. I'm open to other opinions though. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=10824 I'm just a bot <gitbot@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |gitbot@bugs.koha-community. | |org When did the bot| |2013-09-29 last check this| | --- Comment #2 from I'm just a bot <gitbot@bugs.koha-community.org> --- Patch applied cleanly, go forth and signoff -- You are receiving this mail because: You are watching all bug changes.

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=10824 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #21613|0 |1 is obsolete| | --- Comment #4 from David Cook <dcook@prosentient.com.au> --- Created attachment 21630 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=21630&action=edit Bug 10824 - OAI-PMH repository/server not handling time in 'until' and 'from' This patch removes the DATE() function from a query on timestamp, and adds a sub that strips the UTC designators "T" and "Z" from incoming "from" and "until" arguments in OAI-PMH requests so that they're more compliant with MySQL (and probably other databases as well). This means that the date and time for the 'from' and 'until' arguments will be matched correctly in the database. This patch also adds 'T00:00:00Z' to 'from' arguments and 'T23:59:59Z' to until arguments, when only dates are provided via the OAI parameters. The zero time isn't necessary, since MySQL treats '2013-09-30' as '2013-09-30 00:00:00' by default. However, the near midnight time is needed for 'until'. Otherwise, you'll never be able to retrieve a record with a date/time matching the 'until' argument. In summary, this patch adds handling for times as well as dates, which is necessary so that Koha is closer to meeting the actual OAI-PMH spec. TEST PLAN: 0) Note down a selection of timestamps from your biblio table 1) Enable your OAI-PMH server through the global system preferences Web services tab. 2) Craft and submit a similar request to the following in your browser: KOHAINSTANCE/cgi-bin/koha/oai.pl?verb=ListRecords&metadataPrefix=oai_dc& from=2013-09-02T13:44:33Z&until=2013-09-05T13:44:33Z Change the exact dates to accord with your timestamps, but keep the YYYY-MM-DDTHH:MM:SSZ format. 3) Note the unexpected behaviour. A "from" argument with the timestamp 2013-09-02T13:44:33Z will show records from 2013-09-03 but not records from 2013-09-02 even though the timestamp in the database will say "2013-09-02 13:44:33". Also note that records with a timestamp later than 13:44:33 will show up for the day 2013-09-05, even though they shouldn't. 4) APPLY THE PATCH 5) Resubmit the links you tried above 6) Note that the applicable records now appear (or do not appear) in accordance with the precise date/time ranges! -- Developer Note: We could've not stripped the UTC designators and used DATE() around the parameters in the SQL queries, but that would have lost the whole purpose of using times in the "from" arguments, since they would've been generalized to just the dates. I think this is probably the best solution. Admittedly, creating "form_arg" and "until_arg" hashrefs in the ResumptionToken object might not be ideal, but I preferred that to copying the _strip_UTC_designator subroutine into two other objects. Perhaps this sub could go somewhere else and be imported into those other two objects but this seemed to be the most sensible decision. I'm open to other opinions though. -- This latest patch adds full support for date/time for OAI requests -- You are receiving this mail because: You are watching all bug changes.

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=10824 --- Comment #5 from David Cook <dcook@prosentient.com.au> --- In hindsight, I suppose this could technically be called an enhancement, since using a granularity of YYYY-MM-DD instead of YYYY-MM-DDThh:mm:ssZ is valid. Personally, I don't see why we don't use the finer granularity. -- You are receiving this mail because: You are watching all bug changes.

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=10824 --- Comment #7 from David Cook <dcook@prosentient.com.au> --- (In reply to Srdjan Jankovic from comment #6)

Not nitpicking or anything, just curious: why adding T...Z string just to strip it off? Wouldn't it be better to do something on the lines of

$arg = length($date_string) == 10 ? ($date_string .= " $time_string") # 00:00:00 or 23:59:59 : _strip_UTC_designators($date_string);

Fair question. I'm adding T...Z string, because it's only stripped off half the time. If you read through the code, you'll see that "from" and "until" properties retain the T and Z designators. It's only the "from_arg" and "until_arg" properties that have them stripped off. The reasoning here is that the "from" and "until" properties are used to generate the actual resumption token, which requires the T and Z designators, while the "from_arg" and "until_arg" have them stripped off for matching in MySQL. Does that make sense? We want the T and Z for the token, but no T and Z for the database. -- You are receiving this mail because: You are watching all bug changes.

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=10824 I'm just a bot <gitbot@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- When did the bot|2013-09-29 00:00:00 |2013-10-30 last check this| | --- Comment #8 from I'm just a bot <gitbot@bugs.koha-community.org> --- Patch applied cleanly, go forth and signoff -- You are receiving this mail because: You are watching all bug changes.

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=10824 Frédéric Demians <frederic@tamil.fr> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |frederic@tamil.fr -- You are receiving this mail because: You are watching all bug changes.

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=10824 Bernardo Gonzalez Kriegel <bgkriegel@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #21630|0 |1 is obsolete| | --- Comment #10 from Bernardo Gonzalez Kriegel <bgkriegel@gmail.com> --- Created attachment 39271 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=39271&action=edit [SIGNED-OFF] Bug 10824 - OAI-PMH repository/server not handling time in 'until' and 'from' This patch removes the DATE() function from a query on timestamp, and adds a sub that strips the UTC designators "T" and "Z" from incoming "from" and "until" arguments in OAI-PMH requests so that they're more compliant with MySQL (and probably other databases as well). This means that the date and time for the 'from' and 'until' arguments will be matched correctly in the database. This patch also adds 'T00:00:00Z' to 'from' arguments and 'T23:59:59Z' to until arguments, when only dates are provided via the OAI parameters. The zero time isn't necessary, since MySQL treats '2013-09-30' as '2013-09-30 00:00:00' by default. However, the near midnight time is needed for 'until'. Otherwise, you'll never be able to retrieve a record with a date/time matching the 'until' argument. In summary, this patch adds handling for times as well as dates, which is necessary so that Koha is closer to meeting the actual OAI-PMH spec. TEST PLAN: 0) Note down a selection of timestamps from your biblio table 1) Enable your OAI-PMH server through the global system preferences Web services tab. 2) Craft and submit a similar request to the following in your browser: KOHAINSTANCE/cgi-bin/koha/oai.pl?verb=ListRecords&metadataPrefix=oai_dc& from=2013-09-02T13:44:33Z&until=2013-09-05T13:44:33Z Change the exact dates to accord with your timestamps, but keep the YYYY-MM-DDTHH:MM:SSZ format. 3) Note the unexpected behaviour. A "from" argument with the timestamp 2013-09-02T13:44:33Z will show records from 2013-09-03 but not records from 2013-09-02 even though the timestamp in the database will say "2013-09-02 13:44:33". Also note that records with a timestamp later than 13:44:33 will show up for the day 2013-09-05, even though they shouldn't. 4) APPLY THE PATCH 5) Resubmit the links you tried above 6) Note that the applicable records now appear (or do not appear) in accordance with the precise date/time ranges! -- Developer Note: We could've not stripped the UTC designators and used DATE() around the parameters in the SQL queries, but that would have lost the whole purpose of using times in the "from" arguments, since they would've been generalized to just the dates. I think this is probably the best solution. Admittedly, creating "form_arg" and "until_arg" hashrefs in the ResumptionToken object might not be ideal, but I preferred that to copying the _strip_UTC_designator subroutine into two other objects. Perhaps this sub could go somewhere else and be imported into those other two objects but this seemed to be the most sensible decision. I'm open to other opinions though. Signed-off-by: Bernardo Gonzalez Kriegel <bgkriegel@gmail.com> Works, find results with correct timestamp No koha-qa errors -- You are receiving this mail because: You are watching all bug changes.

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=10824 Kyle M Hall <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #39271|0 |1 is obsolete| | --- Comment #11 from Kyle M Hall <kyle@bywatersolutions.com> --- Created attachment 39416 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=39416&action=edit [PASSED QA] Bug 10824 - OAI-PMH repository/server not handling time in 'until' and 'from' This patch removes the DATE() function from a query on timestamp, and adds a sub that strips the UTC designators "T" and "Z" from incoming "from" and "until" arguments in OAI-PMH requests so that they're more compliant with MySQL (and probably other databases as well). This means that the date and time for the 'from' and 'until' arguments will be matched correctly in the database. This patch also adds 'T00:00:00Z' to 'from' arguments and 'T23:59:59Z' to until arguments, when only dates are provided via the OAI parameters. The zero time isn't necessary, since MySQL treats '2013-09-30' as '2013-09-30 00:00:00' by default. However, the near midnight time is needed for 'until'. Otherwise, you'll never be able to retrieve a record with a date/time matching the 'until' argument. In summary, this patch adds handling for times as well as dates, which is necessary so that Koha is closer to meeting the actual OAI-PMH spec. TEST PLAN: 0) Note down a selection of timestamps from your biblio table 1) Enable your OAI-PMH server through the global system preferences Web services tab. 2) Craft and submit a similar request to the following in your browser: KOHAINSTANCE/cgi-bin/koha/oai.pl?verb=ListRecords&metadataPrefix=oai_dc& from=2013-09-02T13:44:33Z&until=2013-09-05T13:44:33Z Change the exact dates to accord with your timestamps, but keep the YYYY-MM-DDTHH:MM:SSZ format. 3) Note the unexpected behaviour. A "from" argument with the timestamp 2013-09-02T13:44:33Z will show records from 2013-09-03 but not records from 2013-09-02 even though the timestamp in the database will say "2013-09-02 13:44:33". Also note that records with a timestamp later than 13:44:33 will show up for the day 2013-09-05, even though they shouldn't. 4) APPLY THE PATCH 5) Resubmit the links you tried above 6) Note that the applicable records now appear (or do not appear) in accordance with the precise date/time ranges! -- Developer Note: We could've not stripped the UTC designators and used DATE() around the parameters in the SQL queries, but that would have lost the whole purpose of using times in the "from" arguments, since they would've been generalized to just the dates. I think this is probably the best solution. Admittedly, creating "form_arg" and "until_arg" hashrefs in the ResumptionToken object might not be ideal, but I preferred that to copying the _strip_UTC_designator subroutine into two other objects. Perhaps this sub could go somewhere else and be imported into those other two objects but this seemed to be the most sensible decision. I'm open to other opinions though. Signed-off-by: Bernardo Gonzalez Kriegel <bgkriegel@gmail.com> Works, find results with correct timestamp No koha-qa errors Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com> -- You are receiving this mail because: You are watching all bug changes.

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=10824 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Severity|enhancement |normal CC| |katrin.fischer@bsz-bw.de -- You are receiving this mail because: You are watching all bug changes.