Bug 27573 - Use of NOW() in SQL should be avoided
Summary: Use of NOW() in SQL should be avoided
Status: CLOSED WONTFIX
Alias: None
Product: Koha
Classification: Unclassified
Component: Architecture, internals, and plumbing (show other bugs)
Version: master
Hardware: All All
: P5 - low enhancement (vote)
Assignee: Bugs List
QA Contact: Testopia
URL:
Keywords:
Depends on: 27511 27574
Blocks:
  Show dependency treegraph
 
Reported: 2021-01-29 08:26 UTC by Martin Renvoize
Modified: 2021-12-13 21:10 UTC (History)
1 user (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Martin Renvoize 2021-01-29 08:26:16 UTC
A few uses of the SQL NOW() function have crept in.. if we want to have consistent dates using the timezone set in the config then we need to handle date parsing before we hit the database as the DB timezone may differ from the instance timezone.
Comment 1 David Cook 2021-02-01 02:39:23 UTC
Better yet we should use UTC dates in the database :D
Comment 2 Martin Renvoize 2021-02-02 09:06:25 UTC
In conversation with Jonathan I discovered we actually set the timezone on the database connection handle.. so this isn't the problem I suspected it was.
Comment 3 David Cook 2021-02-02 23:06:54 UTC
(In reply to Martin Renvoize from comment #2)
> In conversation with Jonathan I discovered we actually set the timezone on
> the database connection handle.. so this isn't the problem I suspected it
> was.

I should've mentioned that before. 

It would still be a problem though if you had 2 different Koha websites pointed at the same database with different timezones, although I suppose these days we use the koha-conf.xml rather than the Apache config to set the timezone, so I suppose we are practically limiting Koha to only ever serving 1 timezone. 

Probably not an issue in most cases but certainly a shortcoming.

For instance, my state (New South Wales) actually contains 2 different timezones. That means that Koha couldn't currently serve every school library in this state with the correct time. 

A quick Google suggests France has 12 timezones, so Koha wouldn't be able to handle all X libraries in France either. 

At some point, we really should consider converting from local time to UTC time in the database. That is, transforming from local time to UTC when storing data, and transforming from UTC to local time when retrieving data.

(This also helps to avoid daylight savings time issues.)
Comment 4 Martin Renvoize 2021-02-03 09:03:35 UTC
That's actually what allot of bug 24850 is about.. rather than converting to UTC however, it converts to 'local server' (as set in the config) time to whatever time (with offset or $timezone attached) timezone and back again.

I'd love some input on that one.. it was a pain to write and thus far I don't think the reviewers have really got the point.. but you've just succinctly stated the exact point in your above comment.
Comment 5 David Cook 2021-02-04 03:30:45 UTC
(In reply to Martin Renvoize from comment #4)
> That's actually what allot of bug 24850 is about.. rather than converting to
> UTC however, it converts to 'local server' (as set in the config) time to
> whatever time (with offset or $timezone attached) timezone and back again.
> 

Actually, Ere corrects my thinking about timestamps and UTC at https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=27463#c10. I think my brain was stuck in PostgreSQL mode. 

> I'd love some input on that one.. it was a pain to write and thus far I
> don't think the reviewers have really got the point.. but you've just
> succinctly stated the exact point in your above comment.

I'll take another look.
Comment 6 David Cook 2021-02-04 03:56:49 UTC
(In reply to Martin Renvoize from comment #4)
> I'd love some input on that one.. it was a pain to write and thus far I
> don't think the reviewers have really got the point.. but you've just
> succinctly stated the exact point in your above comment.

I've added input and hopefully it's well-received. Know that I think you are awesome and my input is intended as being constructive. I also signed off so forgive me my remarks based on that at least ;).