When requesting the REST API we are getting datetimes in the RFC3339 format, for instance: 2024-09-04T06:03:34-03:00 This is retrieving the value that is stored DB, and using the timezone defined in the $KOHA_CONF file. However this format is not correctly into account when we search or, more importantly, when we create/update objects. Examples (Using the Buenos Aires timezome, which is UTC-3 "America/Argentina/Buenos_Aires"): 1. POST /api/v1/patrons with "due_date": "2024-09-09T23:59:00-00:00", And you will get "due_date": "2024-09-09T23:59:00-03:00", in DB: date_due: 2024-09-09 23:59:00 2. GET /api/v1/jobs?q={"enqueued_on":{">":"2024-09-04T09:26:52-03:00"}} is generating the following query SELECT `me`.`id`, `me`.`status`, `me`.`progress`, `me`.`size`, `me`.`borrowernumber`, `me`.`type`, `me`.`queue`, `me`.`data`, `me`.`context`, `me`.`enqueued_on`, `me`.`started_on`, `me`.`ended_on` FROM `background_jobs` `me` WHERE `enqueued_on` > '2024-09-04T09:26:52-03:00' But when you run it manually, you get a warning: | Warning | 1292 | Truncated incorrect datetime value: '2024-09-04T09:26:52-03:00' |
Yeah, bug 36217 is just one example where we send UTC but the server interprets it as whatever local time zone the DB is set to.
For 2. We are not hitting the code that is supposed to deal with rfc3339 (in Koha::Object->attributes_from_api) because the date is in 'q'. Koha/REST/Plugin/Objects.pm 160 my ( $filtered_params, $reserved_params ) = $c->extract_reserved_params($args); 212 # Call the to_model function by reference, if defined 213 if ( defined $filtered_params ) { 214 215 # Apply the mapping function to the passed params 216 $filtered_params = 217 $result_set->attributes_from_api($filtered_params); 218 $filtered_params = 219 $c->build_query_params( $filtered_params, $reserved_params ); 220 } q is in $reserved_params; { q [ [0] "{\"enqueued_on\":{\">\":\"2024-09-04T09:26:52-03:00\"}}" ] }
(In reply to Jonathan Druart from comment #0) > 2. GET /api/v1/jobs?q={"enqueued_on":{">":"2024-09-04T09:26:52-03:00"}} > is generating the following query > > SELECT `me`.`id`, `me`.`status`, `me`.`progress`, `me`.`size`, > `me`.`borrowernumber`, `me`.`type`, `me`.`queue`, `me`.`data`, > `me`.`context`, `me`.`enqueued_on`, `me`.`started_on`, `me`.`ended_on` > FROM `background_jobs` `me` > WHERE `enqueued_on` > '2024-09-04T09:26:52-03:00' > > But when you run it manually, you get a warning: > | Warning | 1292 | Truncated incorrect datetime value: > '2024-09-04T09:26:52-03:00' | Moved to bug 37902.
(In reply to Jonathan Druart from comment #0) > When requesting the REST API we are getting datetimes in the RFC3339 format, > for instance: 2024-09-04T06:03:34-03:00 > > This is retrieving the value that is stored DB, and using the timezone > defined in the $KOHA_CONF file. > > However this format is not correctly into account when we search or, more > importantly, when we create/update objects. > > Examples (Using the Buenos Aires timezome, which is UTC-3 > "America/Argentina/Buenos_Aires"): > > 1. POST /api/v1/patrons with > "due_date": "2024-09-09T23:59:00-00:00", > And you will get > "due_date": "2024-09-09T23:59:00-03:00", > > in DB: date_due: 2024-09-09 23:59:00 Moved to bug 37903.
Not caused by bug 36432.
Created attachment 171401 [details] [review] Bug 37831: Adjust timezone We didn't take into account the server's timezone. This does not feel right, we are not supposed to deal with time zone outside of Koha::DateUtils.