Hi Petri, Thanks for sharing your thoughts on the subject, it's extremely valuable.
On the changing timezone for a country, we've actually discussed this with Adam before he proposed the changes and you're right, it could happen for sure. The storing everything in UTC. We were struggling with Adam on the design whether we should do UTC storage or storing everything with timezone, we knew both would be a fit for Fineract. We were thinking about one use-case where in contrast the timezone storage would help and I'd love to hear your opinion on that. In case we store everything in UTC, any ad-hoc SQL query has to be "transformed" in order to properly show datetimes as a result while in case of storing in the tenant's timezone, chances are the person executing the SQL query is sitting in the same TZ as the tenant is high and that way the result is more easily digestible. Of course in case of a regular UTC storage, we can also transform those times into the tenant's timezone but the thing is, the SQL query could get complicated with all the date functions and stuff. What do you think? Is this a reasonable approach? Thanks! Best, Arnold On Sat, Jun 4, 2022 at 7:31 AM Petri Tuomola <[email protected]> wrote: > 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]> 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 > > >
