Bug 30468

Summary: koha-mysql does not honor Koha's timezone setting
Product: Koha Reporter: Kyle M Hall <kyle>
Component: Architecture, internals, and plumbingAssignee: Kyle M Hall <kyle>
Status: CLOSED FIXED QA Contact: Testopia <testopia>
Severity: normal    
Priority: P5 - low CC: dcook, jonathan.druart, lucas, michalurban177, victor
Version: Main   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: Small patch
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
22.11.00,22.05.06,21.11.12
Attachments: Bug 30468: koha-mysql does not honor Koha's timezone setting
Bug 30468: koha-mysql does not honor Koha's timezone setting
Bug 30468: koha-mysql does not honor Koha's timezone setting
Bug 30468: koha-mysql does not honor Koha's timezone setting
Bug 30468: koha-mysql does not honor Koha's timezone setting
Bug 30468: Restore 'set -e' and remove echos
Bug 30468: Restore 'set -e' and remove echos

Description Kyle M Hall 2022-04-06 12:37:43 UTC
If I run a query like "SELECT NOW()" from a koha report, I will get a different answer than if I had run it from koha-mysql. In Koha, we set the timezone for each database connection. However, koha-mysql does not do this, so instead we are left using the default timezone of the database.
Comment 1 Kyle M Hall 2022-04-06 12:41:48 UTC
Created attachment 132996 [details] [review]
Bug 30468: koha-mysql does not honor Koha's timezone setting

If I run a query like "SELECT NOW()" from a koha report, I will get a different answer than if I had run it from koha-mysql.
In Koha, we set the timezone for each database connection.
However, koha-mysql does not do this, so instead we are left using the default timezone of the database.

Test Plan:
1) Set your time zone to something other than the database time zone
2) run "SELECT NOW()" using debian/scripts/koha-mysql
   *not* /usr/sbin/koha-mysql
3) Note you get the database timezone's current time
4) Apply this patch
5) Repeat step 2
6) Now you get the correct time!
Comment 2 Kyle M Hall 2022-04-06 12:46:20 UTC
Created attachment 132998 [details] [review]
Bug 30468: koha-mysql does not honor Koha's timezone setting

If I run a query like "SELECT NOW()" from a koha report, I will get a different answer than if I had run it from koha-mysql.
In Koha, we set the timezone for each database connection.
However, koha-mysql does not do this, so instead we are left using the default timezone of the database.

Test Plan:
1) Set your time zone to something other than the database time zone
2) run "SELECT NOW()" using debian/scripts/koha-mysql
   *not* /usr/sbin/koha-mysql
3) Note you get the database timezone's current time
4) Apply this patch
5) Repeat step 2
6) Now you get the correct time!
Comment 3 Kyle M Hall 2022-04-06 13:02:33 UTC
Created attachment 132999 [details] [review]
Bug 30468: koha-mysql does not honor Koha's timezone setting

If I run a query like "SELECT NOW()" from a koha report, I will get a different answer than if I had run it from koha-mysql.
In Koha, we set the timezone for each database connection.
However, koha-mysql does not do this, so instead we are left using the default timezone of the database.

Test Plan:
1) Set your time zone to something other than the database time zone
2) run "SELECT NOW()" using debian/scripts/koha-mysql
   *not* /usr/sbin/koha-mysql
3) Note you get the database timezone's current time
4) Apply this patch
5) Repeat step 2
6) Now you get the correct time!
Comment 4 Kyle M Hall 2022-04-06 13:08:09 UTC
Created attachment 133000 [details] [review]
Bug 30468: koha-mysql does not honor Koha's timezone setting

If I run a query like "SELECT NOW()" from a koha report, I will get a different answer than if I had run it from koha-mysql.
In Koha, we set the timezone for each database connection.
However, koha-mysql does not do this, so instead we are left using the default timezone of the database.

Test Plan:
1) Set your time zone to something other than the database time zone
2) run "SELECT NOW()" using debian/scripts/koha-mysql
   *not* /usr/sbin/koha-mysql
3) Note you get the database timezone's current time
4) Apply this patch
5) Repeat step 2
6) Now you get the correct time!
Comment 5 Michal Urban 2022-06-29 17:52:13 UTC
Created attachment 136746 [details] [review]
Bug 30468: koha-mysql does not honor Koha's timezone setting

If I run a query like "SELECT NOW()" from a koha report, I will get a different answer than if I had run it from koha-mysql.
In Koha, we set the timezone for each database connection.
However, koha-mysql does not do this, so instead we are left using the default timezone of the database.

Test Plan:
1) Set your time zone to something other than the database time zone
2) run "SELECT NOW()" using debian/scripts/koha-mysql
   *not* /usr/sbin/koha-mysql
3) Note you get the database timezone's current time
4) Apply this patch
5) Repeat step 2
6) Now you get the correct time!

Signed-off-by: Michal Urban <michalurban177@gmail.com>
Comment 6 David Cook 2022-06-29 23:59:06 UTC
Ohhh good catch.
Comment 7 Jonathan Druart 2022-08-01 10:44:24 UTC
Kyle, do we really want to keep the echos?
Comment 8 Jonathan Druart 2022-08-01 10:50:13 UTC
Created attachment 138457 [details] [review]
Bug 30468: Restore 'set -e' and remove echos
Comment 9 Jonathan Druart 2022-08-01 10:50:51 UTC
What about this patch? Set to PQA if you agree with it.
Comment 10 Kyle M Hall 2022-08-01 12:08:55 UTC
Created attachment 138458 [details] [review]
Bug 30468: Restore 'set -e' and remove echos

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 11 Tomás Cohen Arazi 2022-08-01 12:31:13 UTC
Pushed to master for 22.11.

Nice work everyone, thanks!
Comment 12 Lucas Gass 2022-09-29 15:24:30 UTC
Backported to 22.05.x for 22.05.06
Comment 13 Victor Grousset/tuxayo 2022-10-13 15:58:45 UTC
Not backported to oldoldstable (21.05.x). Feel free to ask if it's needed.

Nothing to document it seems, marking resolved.
Comment 14 Arthur Suzuki 2022-10-22 22:07:58 UTC
Thanks!

Pushed to 21.11 for 21.11.12