Á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 <adamsa...@gmail.com> 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, sif...@skyburgsystems.org 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 <adamsa...@gmail.com>
> > Sent: Friday, 03 June 2022 9:32 AM
> > To: dev@fineract.apache.org
> > Cc: Ádám Sághy <adamsa...@gmail.com>
> > 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
> >
>
