Hi All,
My name is Mike and I just joined this community. My previous experience
is in Payroll sofware but we're looking at fineract for a future
project. I would just like to say that from my own experience Date Time
handling is complicated enough as it is that we don't need to make it
any more complicated. As some have already stated in this thread most
projects store Date Time values in the database as UTC. In a
multi-tenanted, multi-cultural, distributed project such as fineract it
is, in my opinion, the only sane thing to do. Doubly so because finaract
is also an API end-point and we can't control how a current or future
consumer of the API will pass dates to us. In my opinion the best way to
handle this is standardize internally on UTC and convert as necessary at
the application (business logic) boundary. If we do need to store
Timezones its best to store it separately as an IANA Timezone string
(instead of a numeric timezone offset) and do the conversion at run time.
The folks at Blackball Software explain this a lot better than I could:
https://blog.blackballsoftware.com/posts/2017/7/23/handling-and-storing-dates-in-a-globally-distributed-application
Thanks,
Mike.
On 06/06/2022 19:29, James Dailey wrote:
Ádám - This is an important change to get right, and you've definitely
hit upon an important problem in the current implementation -
inconsistent use of time and failure to use timezones.
It is a simple thing yet "endlessly" complex.
As @Petri Tuomola noted, "The way I’ve seen timezones solved in all
the other systems is simply to store all times as GMT/UTC."
If we are taking a different approach, I think we need to be clear on
how our solution becomes superior.
One path, the one most trod, the complexity gets moved to the SQL
queries. :( The other path, the complexity is handled at
configuration / hosting level. Do I have that right?
Is there a case to make that configuration via DevOps is more
sophisticated these days than when these other banking systems were
being designed?
Regardless, there may be some scenarios for which it is very difficult
to have a hard set of logic, as timezones are political, legal, and
technical. We can only solve for the most likely.
Timestamps thus have a firm requirement to be consistent and
transparent system wide.
Back to Petri's point:
Is there a way to have both approaches in your proposal? That is,
have the - {default } option to choose "configure with a single
UTC/GMT" and write that to each tenant - thus devolving to "consistent
with store all times in UTC/GMT"? Would this help with either
simpler configurations or those who want to build on top of that type
of backend config?
To the point about transparency, I also think that there should be
more "awareness in the UI" as to what is "configured system time", and
of course, in reports.
Separately, do we make reference to NTP (Network Time Protocol) for
UTC consistency? As we know, some machines running in local don't
have their internal clocks set correctly and that's a huge problem for
distributed versions of the architecture.
Thoughts?
On Mon, Jun 6, 2022 at 1:25 AM Ádám Sághy <[email protected]> wrote:
Hi Sifiso,
I believe by adding and storing the Timezone details of the date time fields in
the database will not have any impact to the user device locale behaviour.
This approach will not change the way of the Fineract is using the user locale
information.
The proposed solution would change the following things only:
- In the database the TIMESTAMP (without timezone) fields to be changed to
“TIMESTAMP WITH TIME ZONE”
- Instead of fetching/storing (with JPA) these fields as java.util.Date, it will be
“java.time.OffsetDateTime"
- Same applies for the native queries
The main reason is to overcome the probable Daylight Savings issues if only a
“moment" is stored in database (without timezone or offset information)
I hope it helps to understand better.
Should you have any question, please let me know!
Regards
Adam
On 6 Jun 2022, at 08:09, [email protected] wrote:
Hi Adam,
Thank you for sharing. Just wanted to know what the impact of having a
server located in a different continent to the user would be? Using this
approach will it pickup the user device's date settings automatically?
-----Original Message-----
From: Ádám Sághy <[email protected]>
Sent: Friday, 03 June 2022 9:32 AM
To: [email protected]
Cc: Ádám Sághy <[email protected]>
Subject: Timezone issues with Daylight savings
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
