Re: Timezone issues with Daylight savings

[VICTOR MANUEL ROMERO RODRIGUEZ]

System) we follow these order:
>
>
> MONETARY TRANSACTIONS***
> 1. Storing the values in timestamp with milliseconds (8byte) precision of
> any financial transaction, with the Zone Id, we had to add the Accounting
> Datetime (Time Zoned) depending on the channel (branch, mobile, web
> banking, real time systems). Just to mention, if we are migrating from old
> core banking they wont have all the time information or even they could not
> have it at all.
>
> * Receiving Date Time - UTC - Zone Id - Stored and processed as Timestamp
> - milliseconds (8byte) - Display format ISO 8601 -MM-DD HH:mm:ss -
> trusted
> * Processing Date Time - UTC - Zone Id -  Stored and processed
> as  Timestamp milliseconds (8byte) - Display format ISO 8601 -MM-DD
> HH:mm:ss - trusted
> * Client Date Time - Time Zoned -  Stored and processed as DATETIME - Java
> Date Time (Java 8 or above) - ISO 8601 Format -MM-DD HH:mm:ss  - not
> trusted
> * Accounting Date - Time Zoned - Stored and processed as DATETIME - Java
> Date Time (Java 8 or above) - ISO 8601 Format -MM-DD It is linked to
> the related calendar of the financial product
>
> *PERSONAL DATA *
> Birth Dates are required to be verified as part of the customer onboarding
> during the KYC/AML/TF process and check it vs extenals systems like credit
> bureaus or screenings.
>
> * Birth Date - Date without time of day and without timezone - Stored and
> processed as DATETIME - Java Date Time (Java 8 or above) - Display ISO 8601
> Format -MM-DD
> * Calendar Dates - Date without time of day and without timezone - Stored
> and processed as DATETIME - Java Date Time (Java 8 or above) - Display ISO
> 8601 Format -MM-DD
> * Printed date time  in receipts/notes of transactions - Date with time
> of day and with time zone of the location where the transaction was done
> and the receipt/note was printed.  - Stored and processed as DATETIME -
> Java Date Time (Java 8 or above) - Display Format ISO 8601  -MM-DD
> HH:mm:ss
>
> *SCHEDULED DATES*
> * Loan Repayment Dates - Date without time of day and without timezone  -
> Stored and processed as DATETIME - Java Date Time (Java 8 or above) -
> Display ISO 8601 Format -MM-DD
> * Batch Scheduling - String/Status/Boolean (in different fields) - Date with
> time of day and without time zone of the location where the job will be
> run/executed, status and if it was executed successfully or not. Spring 
> @CronExpression
> - - Display i.e. * * * * * * /SCHEDULED/STARTED/RUNNING/ENDED-true/false
> (executed)
>
>  ┌─ second (0-59)
>  │ ┌─ minute (0 - 59)
>  │ │ ┌─ hour (0 - 23)
>  │ │ │ ┌─ day of the month (1 - 31)
>  │ │ │ │ ┌─ month (1 - 12) (or JAN-DEC)
>  │ │ │ │ │ ┌─ day of the week (0 - 7)
>  │ │ │ │ │ │  (or MON-SUN -- 0 or 7 is Sunday)
>  │ │ │ │ │ │
>  * * * * * *
>
>
> *DAYLIGHT SAVING TIME*
> *Technical considerations:
> - All the systems MUST be connected and synchronized to to NTP servers
> (even cloud providers have NTP servers/solutions for synchronize the VM,
> Container Engines, DB, Messaging systems clocks).
> - If it is required by local regulations the system must use the NTP
> servers provided by Federal agencies or the Central Bank. Example in Mexico
> we have the CENAM which is the Metrology National Center and is a trusted
> NTP server for UTC and also for synchronizing the Zoned Time for Mexico.
> * Operational considerations:
> - Introduce/enhance unit/integration test scenarios with different system
> time zones, connection time zone, db time zone, UTC sync with NTP.
> - Introduce and coordinate Fineract group testing, I think that there are
> fineracters that would like to test their systems for sending and receiving
> transactions. We have to do regulatory/compliance group testing with the
> Mexican Central Bank (BANXICO) for sending and receiving transactions and
> the systems are evaluated.
> - All the systems have to be restarted to avoid any cache at OS level or
> JVM level after the DST, this is a standard procedure and best practice,
> even the MFIs must have at least a computer which should be restarted
> frequently for receiving updates or the big ones a mainframe.
> - Operator must verify the batch job running (which could have different
> times during the date depending of strategy/amount of data/regulatory)
> - Batch job alerting - part of the logging and monitoring improvements
> that could be applied to Apache Fineract (we had to connect to BMC suite)
> so then if any job is not running/ending at the scheduled time, the
> operator can check and execute corrective actions.
>
>

Re: Timezone issues with Daylight savings

[VICTOR MANUEL ROMERO RODRIGUEZ]

 to test their systems for sending and receiving
transactions. We have to do regulatory/compliance group testing with the
Mexican Central Bank (BANXICO) for sending and receiving transactions and
the systems are evaluated.
- All the systems have to be restarted to avoid any cache at OS level or
JVM level after the DST, this is a standard procedure and best practice,
even the MFIs must have at least a computer which should be restarted
frequently for receiving updates or the big ones a mainframe.
- Operator must verify the batch job running (which could have different
times during the date depending of strategy/amount of data/regulatory)
- Batch job alerting - part of the logging and monitoring improvements that
could be applied to Apache Fineract (we had to connect to BMC suite) so
then if any job is not running/ending at the scheduled time, the operator
can check and execute corrective actions.

For now these are my two cents contribution, I will write more and give
more real world examples about them :)

Regards

Victor Romero








El mar, 7 jun 2022 a las 21:45,  escribió:

> Noted thanks for clarifying this Adam.
>
> Practically we use CAT over 3 different countries therefore this
> conversion means where we normally had 01:00AM CAT it would now display
> 11:00PM GMT +2 Hours right? If this is the case that would be a significant
> difference from what we are used to and not a very popular scenario.
>
> With regards to applying timestamps with timezones. I think that is a good
> idea, if it may pick the timezone applicable to that region correct to the
> user settings in the tenant table that would be perfect.
>
> Regards,
>
> -Original Message-
> From: Ádám Sághy 
> Sent: Monday, 06 June 2022 10:25 AM
> To: dev@fineract.apache.org
> Cc: Ádám Sághy 
> Subject: Re: Timezone issues with Daylight savings
>
> 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 
> > Sent: Friday, 03 June 2022 9:32 AM
> > To: dev@fineract.apache.org
> > Cc: Ádám Sághy 
> > 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
> >
>
>
>

RE: Timezone issues with Daylight savings

[sifiso]

Noted thanks for clarifying this Adam.

Practically we use CAT over 3 different countries therefore this conversion 
means where we normally had 01:00AM CAT it would now display 11:00PM GMT +2 
Hours right? If this is the case that would be a significant difference from 
what we are used to and not a very popular scenario. 

With regards to applying timestamps with timezones. I think that is a good 
idea, if it may pick the timezone applicable to that region correct to the user 
settings in the tenant table that would be perfect.

Regards,

-Original Message-
From: Ádám Sághy  
Sent: Monday, 06 June 2022 10:25 AM
To: dev@fineract.apache.org
Cc: Ádám Sághy 
Subject: Re: Timezone issues with Daylight savings

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

Re: Timezone issues with Daylight savings

[Michael Telahun]

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

Re: Timezone issues with Daylight savings

[James Dailey]

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

Re: Timezone issues with Daylight savings

[Ádám Sághy]

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

RE: Timezone issues with Daylight savings

[sifiso]

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

Re: Timezone issues with Daylight savings

[Arnold Galovics]

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 
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  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  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
>
>
>

Re: Timezone issues with Daylight savings

[Petri Tuomola]

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  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  > 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

Re: Timezone issues with Daylight savings

[Arnold Galovics]

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  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

Timezone issues with Daylight savings

[Ádám Sághy]

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