Hi Timezones are definitely a “challenge” in every core banking system… it gets even more fun when you consider all the changes that are being made to timezones on an infrequent basis: countries changing timezones / timezones changing offset / countries stopping use of DST etc. The most fun scenario I’ve encountered is when such changes make midnight on a particular day an invalid time instant. One example is 1st January 1982 in SST. In the worst case, this means that if you’ve chosen to represent a date by setting the time component to 00:00:00, then any logic trying to process that date will fail as no such instant exists :-)
The way I’ve seen timezones solved in all the other systems is simply to store all times as GMT/UTC. Any timezones are used only for display/reporting (based on user / branch preferences) or interfaces (e.g. if a domestic clearing requires times/dates in local timezone). Conversion from UTC/GMT to specific timezone is done in runtime - the local times are never stored in the database. This means the transactions etc in the database are always in true chronological order and no timezone etc effects need to be considered when processing them. The only time where this causes some challenges is when deriving values for a specific date (e.g. start of day / end of day) as the point of time to be considered SOD / EOD is of course timezone dependent. But given that typically EOD / SOD batches run at different times for different countries, this is not that difficult to solve… Just a thought - maybe that would be an alternative design approach we could consider for Fineract as well… Regards Petri > On 4 Jun 2022, at 12:16 AM, Arnold Galovics <[email protected]> wrote: > > Hi Adam, > > Thanks for bringing attention to this. > Date handling is definitely something we eventually need to take on. The > issue you mentioned around daylight saving and not being able to keep a > strictly monotonic creation date for transactions is definitely concerning. > > I agree with your proposal, let's add timezone to every single database field. > > Best, > Arnold > > > > On Fri, Jun 3, 2022 at 9:32 AM Ádám Sághy <[email protected] > <mailto:[email protected]>> wrote: > Dear Community, > > I was spending some time to understand in detail the date handling of > Fineract and i might learnt a gap which could be a potential problem when the > tenant (or system) timezone has daylight savings feature. > > Current behaviour: > - Some of the audit datetime fields are using system timezone (usually 3rd > party libs, like: quartz) > - Some of the audit datetime fields are using tenant timezone (usually the > fineract audit features, like: creation date, last modified date) > - We are storing them in DB without timezone attribute > > The problem: > - If a transaction (#1) was done at 2:59 AM 30th of October and 1 minutes > later we are adjusting the clock backward with an hour and the following > incoming a new transaction (#2) then the creation date will be 2:02 AM 30th > of October > > This potentially a huge problem if any logic is depending on the creation > date or using it for audit purposes. > > I would like to propose the following solution: > > - We should introduce Timezone aware datetime handling into Fineract and also > store the timezone attribute for these kind of date in the database as well > > Should you have any question, please let me know! > > Regards, > Adam
