You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@fineract.apache.org by Ádám Sághy <ad...@gmail.com> on 2022/06/03 07:32:17 UTC
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
Posted by Arnold Galovics <ar...@apache.org>.
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 <pe...@gmail.com>
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 <ar...@apache.org> 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 <ad...@gmail.com> 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
Posted by Petri Tuomola <pe...@gmail.com>.
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 <ar...@apache.org> 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 <adamsaghy@gmail.com <ma...@gmail.com>> 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
Posted by Arnold Galovics <ar...@apache.org>.
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 <ad...@gmail.com> 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
Posted by Petri Tuomola <pe...@gmail.com>.
Thanks Adam - I think this looks great, and will be a huge improvement once implemented.
I’m a bit worried about all the places where we need to fix the code - both in Fineract as well as in all the tests - as well as covering all the possible scenarios (e.g. when we care about the date part only, or when we add / subtract dates to work out durations, and when we want to preserve ordering etc). But applying what you describe consistently to every datetime is definitely the right solution in my view - it’s just probably going to be a lot of work!
Regards
Petri
> On 22 Jul 2022, at 4:50 PM, Ádám Sághy <ad...@gmail.com> wrote:
>
> Hi guys,
>
> I am at the implementation phase, so I wanted to share my war plan with you for reviewing:
>
> Date time fields to be stored in UTC at DB side & Migrating from LocalDateTime to OffsetDateTime for TZ clarity
>
> Current
>
> Currently Fineract was working with Date and later LocalDateTime for handling date times.
>
> Neither the Date or the LocalDateTime is storing any explicit timezone information.
> (Internally the Date is working with epoch milliseconds and with system timezone when “actual date time” to be fetched).
> Some of the ‘audit’/date time fields were stored in system timezone
> Some of the other ‘audit’/date time fields were stored in tenant timezone
> Some of the fields when it was fetched from the DB, it was handled in wrong timezone
>
>
> DB side
>
> In mysql
> Majorly DATETIME type is used for storing in DB
>
> In postgres
> TIMESTAMP (WITHOUT TIME ZONE) type is used for storing in DB
>
>
> The common behavior of these types: They are storing AS-IS basis the date time values.
>
> Proposal
>
> DB side
>
> In mysql
> We stick with DATETIME type
>
> RANGE is broader '1000-01-01 00:00:00' to '9999-12-31 23:59:59' vs '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.
> With TIMESTAMP there is back and forth conversion while with DATETIME there is not
> The problem:
> If the session.timezone is UTC, there is no problem
> If the session.timezone is +3:00, the timestamp will be shifter by +3:00, which could lead to potential problems
> Indexing
> Datetime cannot be indexed, but we don’t need this functionality anyway
> Caching
> Queries having datetime cannot be cached, but we don't need this functionality anyway
>
> In postgres
> We change from TIMESTAMP (WITHOUT TIME ZONE) to TIMESTAMP (WITH TIME ZONE)
>
> The values will be stored in UTC and retrieved in UTC
> No conversion by JDBC driver or anything
> The time zone related functions can be used
>
>
> Application side
>
> We are moving away from the LocalDateTime and rather use OffsetDateTime (in some places ZonedDateTime might be used).
>
> PRO
> In every situation we will know explicitly the time zone of the date time
> We avoid situations where the “wrong” time zone is assumed and for that reason the application will work with the wrong date time.
> We do not need to manually take care of time conversions
> Postgres doing it automatically to UTC
> Mysql is doing semi-automatically to UTC
> JDBC connection string to be extended with:
> serverTimezone=UTC
> Our business logic can rely on the explicit time zone of the date time, rather than using assumptions
>
> CON
> We need to change LocalDateTime to OffsetDateTime (or ZonedDateTime) to every places
>
> I would suggest using OffsetDateTime, because the JPA and the JDBC drivers do not support ZonedDateTime!
>
>
> Some examples
>
> System (JVM) timezone: Europe/Budapest
> Tenant timezone: Asia/Kolkata
>
> #1 Store created on date
>
> #1.1 in Postgres
>
> DB
> created_on_utc: TIMESTAMP (WITH TIME ZONE)
>
>
> Spring Auditing
> Default auditing datetime provider is ‘CurrentDateTimeProvider.INSTANCE’
> It is using: LocalDate.now();
> The system (JVM) timezone will be used to create a ‘momentum in time’.
> This is not proper for us!
> The actual date time should be collected in the tenant time zone!
> Custom implementation
> OffsetDateTime.now(TenantZoneId)
> For the new auditable entities
>
>
> JPA
> createdOnDate: OffsetDateTime;
>
> setCreatedOnDate(OffsetDateTime dateTime) {
> this.createdOnDate = dateTime;
> }
>
>
> In DB
> 2022-07-19 16:43:23.125+5:30 -> 2022-07-19 11:13:23.125+0:00
>
> #1.2 in MySql
>
> DB
> created_on_utc: DATETIME
>
>
> Spring Auditing
> Default auditing datetime provider is ‘CurrentDateTimeProvider.INSTANCE’
> It is using: LocalDate.now();
> The system (JVM) timezone will be used to create a ‘momentum in time’.
> This is not proper for us!
> The actual date time should be collected in the tenant time zone!
> Custom implementation
> OffsetDateTime.now(TenantZoneId)
> For the new auditable entities
>
>
> JPA
> createdOnDate: OffsetDateTime;
>
> setCreatedOnDate(OffsetDateTime dateTime) {
> this.createdOnDate = dateTime;
> }
>
> JDBC Driver
> Add “serverTimezone=UTC” to instruct the driver to avoid any unnecessary conversion as we want to store it in UTC at DB side.
>
>
> In DB
> 2022-07-19 11:13:23.125+0:00 -> 2022-07-19 11:13:23.125
>
> #2 Fetch created on date
>
> #2.1 in Postgres
>
> DB
> created_on_utc: TIMESTAMPZ -> 2022-07-19 11:13:23.125+00:00
>
>
> JPA
> During fetching from DB, the OffsetDateTime will be in the system (JVM) timezone, but with the proper value:
> createdOnDate: OffsetDateTime -> 2022-07-19 13:13:23.125+02:00
>
>
> #2.2 in MySql
>
> DB
> created_on_utc: DATETIME -> 2022-07-19 11:13:23.125
>
>
> JPA
> During fetching from DB, the OffsetDateTime will be in the system (JVM) timezone, but with the proper value:
> createdOnDate: OffsetDateTime -> 2022-07-19 13:13:23.125+02:00
>
> #3 Converting to tenant time zone and format to ISO-8601
>
> For converting to tenant time zone
> ZoneId tenantTimeZoneId = ZoneId.of(“Asia/Kolkata”);
> OffsetDateTime createdOnDateWithTenantTZ = createdOnDate.withOffsetSameInstant(tenantTimeZoneId.getRules().getOffset(createdDate.toInstant());
>
> For formatting to ISO-8601
> String dateTimeString = DateTimeFormatter.ISO_OFFSET_DATE_TIME.format(createdOnDate); ->
>
> “2022-07-19T16:43:23+05:30”
>
>
> Changes in TLDR:
> Using TIMESTAMPZ (TIMESTAMP WITH TIME ZONE) for Postgresql
> Migrating from TIMESTAMP (WITHOUT TIME ZONE) to TIMESTAMP (WITH TIME ZONE)
> New columns to be created to avoid loss of data
> No changes at datetime type at MySQL side, but adding “serverTimezone=UTC” JDBC property to avoid unnecessary conversion.
> LocalDateTime to be migrated to OffsetDateTime and used in the application
> Audit and any other date time fields will be created based on the tenant timezone!
> Introducing custom AuditHandler and custom DateTimeProvider
> LocalDateTime.now() - > OffsetDateTime.now(TenantZoneId);
> Make sure whether the date time is sent out in any response to be sent in ISO-8601 format: yyyy-MM-ddTHH:mm:ss.SSS[-HH:MM] (where applicable)
> - There should be an another conversation about the date time serialization!
> I will start a separate thread for that
>
> Should you have any question, please let me know!
>
> Regards,
> Adam
>
>> On 13 Jun 2022, at 08:23, VICTOR MANUEL ROMERO RODRIGUEZ <victor.romero@fintecheando.mx <ma...@fintecheando.mx>> wrote:
>>
>> Hello Adam,
>>
>> I think we should consider two extra topics:
>>
>> - Eclipse JPA Support of the datetime annotation (it will be in charge to do the most of work for storing/retrieving data to the DB, the other is native)
>> - JDBC Driver version
>>
>> https://github.com/eclipse-ee4j/jpa-api/issues/63 <https://github.com/eclipse-ee4j/jpa-api/issues/63>
>> https://docs.oracle.com/javase/8/docs/technotes/guides/jdbc/jdbc_42.html <https://docs.oracle.com/javase/8/docs/technotes/guides/jdbc/jdbc_42.html>
>>
>> I am aware that database work is to store the data efficiently, but datetime has been an issue while doing migrations between database versions or database vendors, but if we store the date time in Timestamp since Unix Epoch Time using numeric values (long) will improve the migration and datetime handling.
>>
>> Just some thoughts expressed in Java Code: https://paste.apache.org/qi06s <https://paste.apache.org/qi06s>
>>
>> In the future the software archaeologists can understand why we choose the use of a long value for storing the transaction datetime in Apache Fineract. :)
>>
>> Using Unix Epoch Time with a real time payment system connected to the Apache Fineract core banking avoids errors in API calls for transactions posted during the "lost hours" of Daylight Saving Time switch.
>>
>> Regards
>>
>> Victor
>>
>>
>>
>> El jue, 9 jun 2022 a las 14:28, Ádám Sághy (<adamsaghy@gmail.com <ma...@gmail.com>>) escribió:
>> Hi guys,
>>
>> Frist of all, thank you very much the many feedback and recommendations! It is always good to see such an active community! :)
>>
>>
>> I have kind of rewritten my proposal to fit as much as possible for your suggestions. (If I missed something, please let me know).
>>
>> Also i am attaching a PDF which lists all the DATETIME fields i have found in Fineract, which we need to consider during the rework and migration, if we go forward with the idea to store them in UTC in the future.
>>
>> I would like to highlight the last column of that list which suggest to change some of these fields from DATETIME to DATE (as we already storing only DATE values there without TIME part or with TIME of 00:00:00)!
>>
>> But now back to the reworked proposal:
>> Currently we are storing the date time fields in db without time zone information.
>> It means a “momentum in time” is stored only which is not tied to any timezone.
>> The consumer must have this information and “add” this extra information while consuming and processing these date times.
>>
>> Currently the date time values are
>>
>> Currently two DB engine is supported
>>
>> - MySQL (5.6 or MariaDB 10.x)
>>
>> - Using “DATETIME” field type - Exemptions may vary
>>
>> - PostgreSQL (13.4)
>>
>> - Using “TIMESTAMP without Time Zone” field type - Exemptions may vary
>>
>> Current behavior
>>
>> - 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
>> - We dont have true chronological order
>>
>> 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.
>>
>>
>>
>> Proposal
>>
>> As many of the members of the Fineract community (and most of the forums on the internet) have already mentioned, the best approach would be to store all date time information in UTC in the database.
>>
>> The conversion should happen on the application level between UTC and the actual tenant timezone.
>>
>> On application level we shall start using ZonedDateTime, which will ensure for us we are working and sending the proper date time according to the tenant timezone.
>>
>> This way in the database we can achieve true chronological order for audit entries / transaction entries.
>>
>> PROs
>>
>> - It will be resilient to political / geographical changes:
>> - Everything is stored in UTC, but if the tenant timezone got adjusted, the entries can
>>
>> remain unchanged, the application will use the new timezone rule and display accordingly the entries
>>
>> - We don’t need to care of Daylight Savings, we shall just use ZonedDateTime and it will use the latest Zone rules to convert the fetched date time to the proper timezoned date time.
>>
>> - We will have true chronological order
>>
>> - Standardized date time timezone in DB (UTC)
>>
>> - We dont need to store timezone id or offset or IANA timezone string, rather we can do the
>>
>> conversion at application level when needed
>>
>> - Data is consistent
>>
>> CONs
>>
>> - We need to implement strict conversion boundaries between application and database
>>
>> - PostgreSql ‘Timestamp with Time Zone’ will store the date time in UTC anyway, but
>>
>> Mysql DATETIME wont. MySql Timestamp would do the same as Postgresql, however
>>
>> MySql timestamp can store date times till ‘2038-01-19 03:14:07’
>>
>> - Querying directly (without the application layer) from the database will be more complex as the
>>
>> date times will be in UTC and that need to be considered during any query.
>>
>> - All the native queries and report queries to be analyzed and updated according to the new
>>
>> strategy
>>
>> - Migration is problematic
>>
>> - Conversion of already existing date time values to UTC is not straightforward - The whole date time handling in Fineract must be refactored
>>
>> - LocalDateTime -> ZonedDateTime - Future dates might be problems
>>
>> - Storing dates as UTC values for future dates may pose issues in the future because we don’t know when daylight saving and timezone rules change.
>>
>> - Storing Recent Past Dates
>>
>> - There is another exception to this rule and that is for events that have happened in the
>>
>> recent past.
>>
>> - This happens when there is a delay between when new timezone rules go into effect and
>>
>> when an application is updated to handle it.
>>
>> Not PRO, not CON but requirements (soft and hard)
>>
>> - Server to be in UTC (not a hard requirement, but would be nice!)
>>
>> - Start using Zone.UTC instead of ZoneId.systemDefault() for truncating or for ‘atStartOfDay’
>>
>> functionalities
>>
>> - Configure JVM timezone to be UTC
>>
>> - Use NTP service for system clock synchronization!
>>
>> - Configure JDBC driver to use UTC as connection timezone
>>
>> - Database timezone to be in UTC (for fail-safety)
>>
>>
>>
>>
>>
>> Example of transaction handling with the newly proposed strategy (During the end of Daylight Savings)
>>
>> In TLDR:
>> Actions
>>
>> - DB layer: Store all datetime in UTC (convert to UTC on application layer)
>>
>> - Application layer: UTC to be converted to tenant timezone and work with it.
>>
>> Challenges
>>
>> - Conversion back and forth from and to UTC
>>
>> - Data migration
>>
>> - Existing “timezone unaware” date times and their conversion to UTC
>>
>> - Mysql and Postgres datetime types
>>
>> - TIMESTAMP VS DATETIME
>>
>> - Should we change from “without Time Zone” to “with Time Zone” date
>>
>> type in DB?
>>
>> - Existing native queries and report queries
>>
>> - Future dates
>>
>> - Do we have any?
>>
>> - Recent past dates
>>
>> - Can be avoided by keeping up-to-date the Zone rules information
>>
>>
>>
>> Should you have any question, please let me know!
>>
>>
>> Regards,
>> Adam
>>
>>
>>> On 8 Jun 2022, at 08:50, VICTOR MANUEL ROMERO RODRIGUEZ <victor.romero@fintecheando.mx <ma...@fintecheando.mx>> wrote:
>>>
>>> Hello Fineract Community,
>>>
>>> TL;DR
>>> Consider the type of time that we want to solve using Fineract: actual event time, broadcast time, relative time, historical time, recurring time.
>>>
>>> UTC - is not silver bullet, but important for monetary transactions
>>> TimeZone - Required by regulations
>>> Future dates - Loan repayments and jobs
>>> Date format ISO 8601
>>>
>>> Now the reasoning and long explanation:
>>>
>>> I would like to remark that it is important to separate two concepts which are causing confusion.
>>>
>>> UTC is a constant defined a long time ago for clock synchronization and making fault-tolerant distributed real-time systems. It must be used for any enterprise class system and Apache Fineract is moving to be a framework for handling this as a native solution. It must be transparent for the end.
>>>
>>> The users of the Apache Fineract must be able to set Time Zones, Offsets (hey remember these are two different concepts too) and Date Format as per local regulation, political reasons, or any other. But all of them follow the Format ISO 8601 (aka ISO Date). with ISO 8601 values are ordered from the largest to smallest unit of time: year, month (or week), day, hour, minute, second, millisecond.
>>>
>>> For the challenges that we had to solve some years ago and still now is useful (now we are applying it to the Mifos Payment Hub connected to the Mexican Real Time 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 YYYY-MM-DD HH:mm:ss - trusted
>>> * Processing Date Time - UTC - Zone Id - Stored and processed as Timestamp milliseconds (8byte) - Display format ISO 8601 YYYY-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 YYYY-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 YYYY-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 YYYY-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 YYYY-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 YYYY-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 YYYY-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.
>>>
>>> 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, <sifiso@skyburgsystems.org <ma...@skyburgsystems.org>> 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 <adamsaghy@gmail.com <ma...@gmail.com>>
>>> Sent: Monday, 06 June 2022 10:25 AM
>>> To: dev@fineract.apache.org <ma...@fineract.apache.org>
>>> Cc: Ádám Sághy <adamsaghy@gmail.com <ma...@gmail.com>>
>>> 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, sifiso@skyburgsystems.org <ma...@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 <adamsaghy@gmail.com <ma...@gmail.com>>
>>> > Sent: Friday, 03 June 2022 9:32 AM
>>> > To: dev@fineract.apache.org <ma...@fineract.apache.org>
>>> > Cc: Ádám Sághy <adamsaghy@gmail.com <ma...@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
>>> >
>>>
>>>
>>
>
Re: Timezone issues with Daylight savings
Posted by Ádám Sághy <ad...@gmail.com>.
Hi guys,
I am at the implementation phase, so I wanted to share my war plan with you for reviewing:
Date time fields to be stored in UTC at DB side & Migrating from LocalDateTime to OffsetDateTime for TZ clarity
Current
Currently Fineract was working with Date and later LocalDateTime for handling date times.
Neither the Date or the LocalDateTime is storing any explicit timezone information.
(Internally the Date is working with epoch milliseconds and with system timezone when “actual date time” to be fetched).
Some of the ‘audit’/date time fields were stored in system timezone
Some of the other ‘audit’/date time fields were stored in tenant timezone
Some of the fields when it was fetched from the DB, it was handled in wrong timezone
DB side
In mysql
Majorly DATETIME type is used for storing in DB
In postgres
TIMESTAMP (WITHOUT TIME ZONE) type is used for storing in DB
The common behavior of these types: They are storing AS-IS basis the date time values.
Proposal
DB side
In mysql
We stick with DATETIME type
RANGE is broader '1000-01-01 00:00:00' to '9999-12-31 23:59:59' vs '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.
With TIMESTAMP there is back and forth conversion while with DATETIME there is not
The problem:
If the session.timezone is UTC, there is no problem
If the session.timezone is +3:00, the timestamp will be shifter by +3:00, which could lead to potential problems
Indexing
Datetime cannot be indexed, but we don’t need this functionality anyway
Caching
Queries having datetime cannot be cached, but we don't need this functionality anyway
In postgres
We change from TIMESTAMP (WITHOUT TIME ZONE) to TIMESTAMP (WITH TIME ZONE)
The values will be stored in UTC and retrieved in UTC
No conversion by JDBC driver or anything
The time zone related functions can be used
Application side
We are moving away from the LocalDateTime and rather use OffsetDateTime (in some places ZonedDateTime might be used).
PRO
In every situation we will know explicitly the time zone of the date time
We avoid situations where the “wrong” time zone is assumed and for that reason the application will work with the wrong date time.
We do not need to manually take care of time conversions
Postgres doing it automatically to UTC
Mysql is doing semi-automatically to UTC
JDBC connection string to be extended with:
serverTimezone=UTC
Our business logic can rely on the explicit time zone of the date time, rather than using assumptions
CON
We need to change LocalDateTime to OffsetDateTime (or ZonedDateTime) to every places
I would suggest using OffsetDateTime, because the JPA and the JDBC drivers do not support ZonedDateTime!
Some examples
System (JVM) timezone: Europe/Budapest
Tenant timezone: Asia/Kolkata
#1 Store created on date
#1.1 in Postgres
DB
created_on_utc: TIMESTAMP (WITH TIME ZONE)
Spring Auditing
Default auditing datetime provider is ‘CurrentDateTimeProvider.INSTANCE’
It is using: LocalDate.now();
The system (JVM) timezone will be used to create a ‘momentum in time’.
This is not proper for us!
The actual date time should be collected in the tenant time zone!
Custom implementation
OffsetDateTime.now(TenantZoneId)
For the new auditable entities
JPA
createdOnDate: OffsetDateTime;
setCreatedOnDate(OffsetDateTime dateTime) {
this.createdOnDate = dateTime;
}
In DB
2022-07-19 16:43:23.125+5:30 -> 2022-07-19 11:13:23.125+0:00
#1.2 in MySql
DB
created_on_utc: DATETIME
Spring Auditing
Default auditing datetime provider is ‘CurrentDateTimeProvider.INSTANCE’
It is using: LocalDate.now();
The system (JVM) timezone will be used to create a ‘momentum in time’.
This is not proper for us!
The actual date time should be collected in the tenant time zone!
Custom implementation
OffsetDateTime.now(TenantZoneId)
For the new auditable entities
JPA
createdOnDate: OffsetDateTime;
setCreatedOnDate(OffsetDateTime dateTime) {
this.createdOnDate = dateTime;
}
JDBC Driver
Add “serverTimezone=UTC” to instruct the driver to avoid any unnecessary conversion as we want to store it in UTC at DB side.
In DB
2022-07-19 11:13:23.125+0:00 -> 2022-07-19 11:13:23.125
#2 Fetch created on date
#2.1 in Postgres
DB
created_on_utc: TIMESTAMPZ -> 2022-07-19 11:13:23.125+00:00
JPA
During fetching from DB, the OffsetDateTime will be in the system (JVM) timezone, but with the proper value:
createdOnDate: OffsetDateTime -> 2022-07-19 13:13:23.125+02:00
#2.2 in MySql
DB
created_on_utc: DATETIME -> 2022-07-19 11:13:23.125
JPA
During fetching from DB, the OffsetDateTime will be in the system (JVM) timezone, but with the proper value:
createdOnDate: OffsetDateTime -> 2022-07-19 13:13:23.125+02:00
#3 Converting to tenant time zone and format to ISO-8601
For converting to tenant time zone
ZoneId tenantTimeZoneId = ZoneId.of(“Asia/Kolkata”);
OffsetDateTime createdOnDateWithTenantTZ = createdOnDate.withOffsetSameInstant(tenantTimeZoneId.getRules().getOffset(createdDate.toInstant());
For formatting to ISO-8601
String dateTimeString = DateTimeFormatter.ISO_OFFSET_DATE_TIME.format(createdOnDate); ->
“2022-07-19T16:43:23+05:30”
Changes in TLDR:
Using TIMESTAMPZ (TIMESTAMP WITH TIME ZONE) for Postgresql
Migrating from TIMESTAMP (WITHOUT TIME ZONE) to TIMESTAMP (WITH TIME ZONE)
New columns to be created to avoid loss of data
No changes at datetime type at MySQL side, but adding “serverTimezone=UTC” JDBC property to avoid unnecessary conversion.
LocalDateTime to be migrated to OffsetDateTime and used in the application
Audit and any other date time fields will be created based on the tenant timezone!
Introducing custom AuditHandler and custom DateTimeProvider
LocalDateTime.now() - > OffsetDateTime.now(TenantZoneId);
Make sure whether the date time is sent out in any response to be sent in ISO-8601 format: yyyy-MM-ddTHH:mm:ss.SSS[-HH:MM] (where applicable)
- There should be an another conversation about the date time serialization!
I will start a separate thread for that
Should you have any question, please let me know!
Regards,
Adam
> On 13 Jun 2022, at 08:23, VICTOR MANUEL ROMERO RODRIGUEZ <vi...@fintecheando.mx> wrote:
>
> Hello Adam,
>
> I think we should consider two extra topics:
>
> - Eclipse JPA Support of the datetime annotation (it will be in charge to do the most of work for storing/retrieving data to the DB, the other is native)
> - JDBC Driver version
>
> https://github.com/eclipse-ee4j/jpa-api/issues/63 <https://github.com/eclipse-ee4j/jpa-api/issues/63>
> https://docs.oracle.com/javase/8/docs/technotes/guides/jdbc/jdbc_42.html <https://docs.oracle.com/javase/8/docs/technotes/guides/jdbc/jdbc_42.html>
>
> I am aware that database work is to store the data efficiently, but datetime has been an issue while doing migrations between database versions or database vendors, but if we store the date time in Timestamp since Unix Epoch Time using numeric values (long) will improve the migration and datetime handling.
>
> Just some thoughts expressed in Java Code: https://paste.apache.org/qi06s <https://paste.apache.org/qi06s>
>
> In the future the software archaeologists can understand why we choose the use of a long value for storing the transaction datetime in Apache Fineract. :)
>
> Using Unix Epoch Time with a real time payment system connected to the Apache Fineract core banking avoids errors in API calls for transactions posted during the "lost hours" of Daylight Saving Time switch.
>
> Regards
>
> Victor
>
>
>
> El jue, 9 jun 2022 a las 14:28, Ádám Sághy (<adamsaghy@gmail.com <ma...@gmail.com>>) escribió:
> Hi guys,
>
> Frist of all, thank you very much the many feedback and recommendations! It is always good to see such an active community! :)
>
>
> I have kind of rewritten my proposal to fit as much as possible for your suggestions. (If I missed something, please let me know).
>
> Also i am attaching a PDF which lists all the DATETIME fields i have found in Fineract, which we need to consider during the rework and migration, if we go forward with the idea to store them in UTC in the future.
>
> I would like to highlight the last column of that list which suggest to change some of these fields from DATETIME to DATE (as we already storing only DATE values there without TIME part or with TIME of 00:00:00)!
>
> But now back to the reworked proposal:
> Currently we are storing the date time fields in db without time zone information.
> It means a “momentum in time” is stored only which is not tied to any timezone.
> The consumer must have this information and “add” this extra information while consuming and processing these date times.
>
> Currently the date time values are
>
> Currently two DB engine is supported
>
> - MySQL (5.6 or MariaDB 10.x)
>
> - Using “DATETIME” field type - Exemptions may vary
>
> - PostgreSQL (13.4)
>
> - Using “TIMESTAMP without Time Zone” field type - Exemptions may vary
>
> Current behavior
>
> - 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
> - We dont have true chronological order
>
> 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.
>
>
>
> Proposal
>
> As many of the members of the Fineract community (and most of the forums on the internet) have already mentioned, the best approach would be to store all date time information in UTC in the database.
>
> The conversion should happen on the application level between UTC and the actual tenant timezone.
>
> On application level we shall start using ZonedDateTime, which will ensure for us we are working and sending the proper date time according to the tenant timezone.
>
> This way in the database we can achieve true chronological order for audit entries / transaction entries.
>
> PROs
>
> - It will be resilient to political / geographical changes:
> - Everything is stored in UTC, but if the tenant timezone got adjusted, the entries can
>
> remain unchanged, the application will use the new timezone rule and display accordingly the entries
>
> - We don’t need to care of Daylight Savings, we shall just use ZonedDateTime and it will use the latest Zone rules to convert the fetched date time to the proper timezoned date time.
>
> - We will have true chronological order
>
> - Standardized date time timezone in DB (UTC)
>
> - We dont need to store timezone id or offset or IANA timezone string, rather we can do the
>
> conversion at application level when needed
>
> - Data is consistent
>
> CONs
>
> - We need to implement strict conversion boundaries between application and database
>
> - PostgreSql ‘Timestamp with Time Zone’ will store the date time in UTC anyway, but
>
> Mysql DATETIME wont. MySql Timestamp would do the same as Postgresql, however
>
> MySql timestamp can store date times till ‘2038-01-19 03:14:07’
>
> - Querying directly (without the application layer) from the database will be more complex as the
>
> date times will be in UTC and that need to be considered during any query.
>
> - All the native queries and report queries to be analyzed and updated according to the new
>
> strategy
>
> - Migration is problematic
>
> - Conversion of already existing date time values to UTC is not straightforward - The whole date time handling in Fineract must be refactored
>
> - LocalDateTime -> ZonedDateTime - Future dates might be problems
>
> - Storing dates as UTC values for future dates may pose issues in the future because we don’t know when daylight saving and timezone rules change.
>
> - Storing Recent Past Dates
>
> - There is another exception to this rule and that is for events that have happened in the
>
> recent past.
>
> - This happens when there is a delay between when new timezone rules go into effect and
>
> when an application is updated to handle it.
>
> Not PRO, not CON but requirements (soft and hard)
>
> - Server to be in UTC (not a hard requirement, but would be nice!)
>
> - Start using Zone.UTC instead of ZoneId.systemDefault() for truncating or for ‘atStartOfDay’
>
> functionalities
>
> - Configure JVM timezone to be UTC
>
> - Use NTP service for system clock synchronization!
>
> - Configure JDBC driver to use UTC as connection timezone
>
> - Database timezone to be in UTC (for fail-safety)
>
>
>
>
>
> Example of transaction handling with the newly proposed strategy (During the end of Daylight Savings)
>
> In TLDR:
> Actions
>
> - DB layer: Store all datetime in UTC (convert to UTC on application layer)
>
> - Application layer: UTC to be converted to tenant timezone and work with it.
>
> Challenges
>
> - Conversion back and forth from and to UTC
>
> - Data migration
>
> - Existing “timezone unaware” date times and their conversion to UTC
>
> - Mysql and Postgres datetime types
>
> - TIMESTAMP VS DATETIME
>
> - Should we change from “without Time Zone” to “with Time Zone” date
>
> type in DB?
>
> - Existing native queries and report queries
>
> - Future dates
>
> - Do we have any?
>
> - Recent past dates
>
> - Can be avoided by keeping up-to-date the Zone rules information
>
>
>
> Should you have any question, please let me know!
>
>
> Regards,
> Adam
>
>
>> On 8 Jun 2022, at 08:50, VICTOR MANUEL ROMERO RODRIGUEZ <victor.romero@fintecheando.mx <ma...@fintecheando.mx>> wrote:
>>
>> Hello Fineract Community,
>>
>> TL;DR
>> Consider the type of time that we want to solve using Fineract: actual event time, broadcast time, relative time, historical time, recurring time.
>>
>> UTC - is not silver bullet, but important for monetary transactions
>> TimeZone - Required by regulations
>> Future dates - Loan repayments and jobs
>> Date format ISO 8601
>>
>> Now the reasoning and long explanation:
>>
>> I would like to remark that it is important to separate two concepts which are causing confusion.
>>
>> UTC is a constant defined a long time ago for clock synchronization and making fault-tolerant distributed real-time systems. It must be used for any enterprise class system and Apache Fineract is moving to be a framework for handling this as a native solution. It must be transparent for the end.
>>
>> The users of the Apache Fineract must be able to set Time Zones, Offsets (hey remember these are two different concepts too) and Date Format as per local regulation, political reasons, or any other. But all of them follow the Format ISO 8601 (aka ISO Date). with ISO 8601 values are ordered from the largest to smallest unit of time: year, month (or week), day, hour, minute, second, millisecond.
>>
>> For the challenges that we had to solve some years ago and still now is useful (now we are applying it to the Mifos Payment Hub connected to the Mexican Real Time 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 YYYY-MM-DD HH:mm:ss - trusted
>> * Processing Date Time - UTC - Zone Id - Stored and processed as Timestamp milliseconds (8byte) - Display format ISO 8601 YYYY-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 YYYY-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 YYYY-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 YYYY-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 YYYY-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 YYYY-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 YYYY-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.
>>
>> 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, <sifiso@skyburgsystems.org <ma...@skyburgsystems.org>> 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 <adamsaghy@gmail.com <ma...@gmail.com>>
>> Sent: Monday, 06 June 2022 10:25 AM
>> To: dev@fineract.apache.org <ma...@fineract.apache.org>
>> Cc: Ádám Sághy <adamsaghy@gmail.com <ma...@gmail.com>>
>> 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, sifiso@skyburgsystems.org <ma...@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 <adamsaghy@gmail.com <ma...@gmail.com>>
>> > Sent: Friday, 03 June 2022 9:32 AM
>> > To: dev@fineract.apache.org <ma...@fineract.apache.org>
>> > Cc: Ádám Sághy <adamsaghy@gmail.com <ma...@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
>> >
>>
>>
>
Re: Timezone issues with Daylight savings
Posted by VICTOR MANUEL ROMERO RODRIGUEZ <vi...@fintecheando.mx>.
Hello Adam,
I think we should consider two extra topics:
- Eclipse JPA Support of the datetime annotation (it will be in charge to
do the most of work for storing/retrieving data to the DB, the other is
native)
- JDBC Driver version
https://github.com/eclipse-ee4j/jpa-api/issues/63
https://docs.oracle.com/javase/8/docs/technotes/guides/jdbc/jdbc_42.html
I am aware that database work is to store the data efficiently, but
datetime has been an issue while doing migrations between database versions
or database vendors, but if we store the date time in Timestamp since Unix
Epoch Time *using numeric values* (long) will improve the migration and
datetime handling.
Just some thoughts expressed in Java Code: https://paste.apache.org/qi06s
In the future the software archaeologists can understand why we choose the
use of a long value for storing the transaction datetime in Apache
Fineract. :)
Using Unix Epoch Time with a real time payment system connected to the
Apache Fineract core banking avoids errors in API calls for transactions
posted during the "lost hours" of Daylight Saving Time switch.
Regards
Victor
El jue, 9 jun 2022 a las 14:28, Ádám Sághy (<ad...@gmail.com>) escribió:
> Hi guys,
> Frist of all, thank you very much the many feedback and recommendations!
> It is always good to see such an active community! :)
>
>
> I have kind of rewritten my proposal to fit as much as possible for your
> suggestions. (If I missed something, please let me know).
>
> Also i am attaching a PDF which lists all the DATETIME fields i have found
> in Fineract, which we need to consider during the rework and migration, if
> we go forward with the idea to store them in UTC in the future.
>
> I would like to highlight the last column of that list which suggest to
> change some of these fields from DATETIME to DATE (as we already storing
> only DATE values there without TIME part or with TIME of 00:00:00)!
>
> But now back to the reworked proposal:
>
> Currently we are storing the date time fields in db without time zone
> information.
> It means a “momentum in time” is stored only which is not tied to any
> timezone.
> The consumer must have this information and “add” this extra information
> while consuming and processing these date times.
>
> Currently the date time values are
>
> Currently two DB engine is supported
>
> - MySQL (5.6 or MariaDB 10.x)
>
> - Using “DATETIME” field type - Exemptions may vary
>
> - PostgreSQL (13.4)
>
> - Using “TIMESTAMP without Time Zone” field type - Exemptions may vary
>
> Current behavior
>
> - 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
> - We dont have true chronological order
>
> 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.
>
>
> *Proposal*
>
> As many of the members of the Fineract community (and most of the forums
> on the internet) have already mentioned, the best approach would be to
> store all date time information in UTC in the database.
>
> The conversion should happen on the application level between UTC and the
> actual tenant timezone.
>
> On application level we shall start using ZonedDateTime, which will ensure
> for us we are working and sending the proper date time according to the
> tenant timezone.
>
> This way in the database we can achieve true chronological order for audit
> entries / transaction entries.
>
> PROs
>
> - It will be resilient to political / geographical changes:
> - Everything is stored in UTC, but if the tenant timezone got adjusted,
> the entries can
>
> remain unchanged, the application will use the new timezone rule and
> display accordingly the entries
>
> -
>
> - We don’t need to care of Daylight Savings, we shall just use
> ZonedDateTime and it will use the latest Zone rules to convert the fetched
> date time to the proper timezoned date time.
> -
>
> - We will have true chronological order
> -
>
> - Standardized date time timezone in DB (UTC)
> -
>
> - We dont need to store timezone id or offset or IANA timezone
> string, rather we can do the
>
> conversion at application level when needed
> -
>
> - Data is consistent
>
> CONs
> -
>
> - We need to implement strict conversion boundaries between
> application and database
>
> - PostgreSql ‘Timestamp with Time Zone’ will store the date time in UTC
> anyway, but
>
> Mysql DATETIME wont. MySql Timestamp would do the same as Postgresql,
> however
>
> MySql timestamp can store date times till ‘2038-01-19 03:14:07’
>
> -
>
> - Querying directly (without the application layer) from the database
> will be more complex as the
>
> date times will be in UTC and that need to be considered during any
> query.
> -
>
> - All the native queries and report queries to be analyzed and
> updated according to the new
>
> strategy
> -
>
> - Migration is problematic
>
> - Conversion of already existing date time values to UTC is not
> straightforward - The whole date time handling in Fineract must be
> refactored
>
> - LocalDateTime -> ZonedDateTime - Future dates might be problems
>
> - Storing dates as UTC values for future dates may pose issues in the
> future because we don’t know when daylight saving and timezone rules change.
>
> - Storing Recent Past Dates
>
> -
>
> - There is another exception to this rule and that is for events that
> have happened in the
>
> recent past.
> -
>
> - This happens when there is a delay between when new timezone rules
> go into effect and
>
> when an application is updated to handle it.
>
> Not PRO, not CON but requirements (soft and hard)
>
> -
>
> - Server to be in UTC (not a hard requirement, but would be nice!)
> -
>
> - Start using Zone.UTC instead of ZoneId.systemDefault() for
> truncating or for ‘atStartOfDay’
>
> functionalities
> -
>
> - Configure JVM timezone to be UTC
> -
>
> - Use NTP service for system clock synchronization!
> -
>
> - Configure JDBC driver to use UTC as connection timezone
> -
>
> - Database timezone to be in UTC (for fail-safety)
>
>
>
> Example of transaction handling with the newly proposed strategy
> (During the end of Daylight Savings)
>
> In TLDR:
> Actions
>
> -
>
> - DB layer: Store all datetime in UTC (convert to UTC on application
> layer)
> -
>
> - Application layer: UTC to be converted to tenant timezone and work
> with it.
>
> Challenges
>
> - Conversion back and forth from and to UTC
>
> - Data migration
>
> - Existing “timezone unaware” date times and their conversion to UTC
>
> -
>
> - Mysql and Postgres datetime types
> -
>
> - TIMESTAMP VS DATETIME
> -
>
> - Should we change from “without Time Zone” to “with Time Zone”
> date
>
> type in DB?
> -
>
> - Existing native queries and report queries
> -
>
> - Future dates
>
> - Do we have any?
>
> - Recent past dates
>
> - Can be avoided by keeping up-to-date the Zone rules information
>
>
> Should you have any question, please let me know!
>
>
> Regards,
> Adam
>
>
> On 8 Jun 2022, at 08:50, VICTOR MANUEL ROMERO RODRIGUEZ <
> victor.romero@fintecheando.mx> wrote:
>
> Hello Fineract Community,
>
> TL;DR
> Consider the type of time that we want to solve using Fineract: actual
> event time, broadcast time, relative time, historical time, recurring time.
>
> UTC - is not silver bullet, but important for monetary transactions
> TimeZone - Required by regulations
> Future dates - Loan repayments and jobs
> Date format ISO 8601
>
> Now the reasoning and long explanation:
>
> I would like to remark that it is important to separate two concepts which
> are causing confusion.
>
> UTC is a constant defined a long time ago for clock synchronization and
> making fault-tolerant distributed real-time systems. It must be used for
> any enterprise class system and Apache Fineract is moving to be a framework
> for handling this as a native solution. It must be transparent for the end.
>
> The users of the Apache Fineract must be able to set Time Zones, Offsets
> (hey remember these are two different concepts too) and Date Format as per
> local regulation, political reasons, or any other. But all of them follow
> the Format ISO 8601 (aka ISO Date). with ISO 8601 values are ordered from
> the largest to smallest unit of time: year, month (or week), day, hour,
> minute, second, millisecond.
>
> For the challenges that we had to solve some years ago and still now is
> useful (now we are applying it to the Mifos Payment Hub connected to the
> Mexican Real Time 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 YYYY-MM-DD HH:mm:ss -
> trusted
> * Processing Date Time - UTC - Zone Id - Stored and processed
> as Timestamp milliseconds (8byte) - Display format ISO 8601 YYYY-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 YYYY-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 YYYY-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 YYYY-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 YYYY-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 YYYY-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 YYYY-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.
>
> 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, <si...@skyburgsystems.org> 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 <ad...@gmail.com>
>> Sent: Monday, 06 June 2022 10:25 AM
>> To: dev@fineract.apache.org
>> Cc: Ádám Sághy <ad...@gmail.com>
>> 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, sifiso@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 <ad...@gmail.com>
>> > Sent: Friday, 03 June 2022 9:32 AM
>> > To: dev@fineract.apache.org
>> > Cc: Ádám Sághy <ad...@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
>> >
>>
>>
>>
>
Re: Timezone issues with Daylight savings
Posted by Ádám Sághy <ad...@gmail.com>.
Hi guys,
Frist of all, thank you very much the many feedback and recommendations! It is always good to see such an active community! :)
I have kind of rewritten my proposal to fit as much as possible for your suggestions. (If I missed something, please let me know).
Also i am attaching a PDF which lists all the DATETIME fields i have found in Fineract, which we need to consider during the rework and migration, if we go forward with the idea to store them in UTC in the future.
I would like to highlight the last column of that list which suggest to change some of these fields from DATETIME to DATE (as we already storing only DATE values there without TIME part or with TIME of 00:00:00)!
But now back to the reworked proposal:
Currently we are storing the date time fields in db without time zone information.
It means a “momentum in time” is stored only which is not tied to any timezone.
The consumer must have this information and “add” this extra information while consuming and processing these date times.
Currently the date time values are
Currently two DB engine is supported
- MySQL (5.6 or MariaDB 10.x)
- Using “DATETIME” field type - Exemptions may vary
- PostgreSQL (13.4)
- Using “TIMESTAMP without Time Zone” field type - Exemptions may vary
Current behavior
- 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
- We dont have true chronological order
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.
Proposal
As many of the members of the Fineract community (and most of the forums on the internet) have already mentioned, the best approach would be to store all date time information in UTC in the database.
The conversion should happen on the application level between UTC and the actual tenant timezone.
On application level we shall start using ZonedDateTime, which will ensure for us we are working and sending the proper date time according to the tenant timezone.
This way in the database we can achieve true chronological order for audit entries / transaction entries.
PROs
- It will be resilient to political / geographical changes:
- Everything is stored in UTC, but if the tenant timezone got adjusted, the entries can
remain unchanged, the application will use the new timezone rule and display accordingly the entries
- We don’t need to care of Daylight Savings, we shall just use ZonedDateTime and it will use the latest Zone rules to convert the fetched date time to the proper timezoned date time.
- We will have true chronological order
- Standardized date time timezone in DB (UTC)
- We dont need to store timezone id or offset or IANA timezone string, rather we can do the
conversion at application level when needed
- Data is consistent
CONs
- We need to implement strict conversion boundaries between application and database
- PostgreSql ‘Timestamp with Time Zone’ will store the date time in UTC anyway, but
Mysql DATETIME wont. MySql Timestamp would do the same as Postgresql, however
MySql timestamp can store date times till ‘2038-01-19 03:14:07’
- Querying directly (without the application layer) from the database will be more complex as the
date times will be in UTC and that need to be considered during any query.
- All the native queries and report queries to be analyzed and updated according to the new
strategy
- Migration is problematic
- Conversion of already existing date time values to UTC is not straightforward - The whole date time handling in Fineract must be refactored
- LocalDateTime -> ZonedDateTime - Future dates might be problems
- Storing dates as UTC values for future dates may pose issues in the future because we don’t know when daylight saving and timezone rules change.
- Storing Recent Past Dates
- There is another exception to this rule and that is for events that have happened in the
recent past.
- This happens when there is a delay between when new timezone rules go into effect and
when an application is updated to handle it.
Not PRO, not CON but requirements (soft and hard)
- Server to be in UTC (not a hard requirement, but would be nice!)
- Start using Zone.UTC instead of ZoneId.systemDefault() for truncating or for ‘atStartOfDay’
functionalities
- Configure JVM timezone to be UTC
- Use NTP service for system clock synchronization!
- Configure JDBC driver to use UTC as connection timezone
- Database timezone to be in UTC (for fail-safety)
Example of transaction handling with the newly proposed strategy (During the end of Daylight Savings)
In TLDR:
Actions
- DB layer: Store all datetime in UTC (convert to UTC on application layer)
- Application layer: UTC to be converted to tenant timezone and work with it.
Challenges
- Conversion back and forth from and to UTC
- Data migration
- Existing “timezone unaware” date times and their conversion to UTC
- Mysql and Postgres datetime types
- TIMESTAMP VS DATETIME
- Should we change from “without Time Zone” to “with Time Zone” date
type in DB?
- Existing native queries and report queries
- Future dates
- Do we have any?
- Recent past dates
- Can be avoided by keeping up-to-date the Zone rules information
Should you have any question, please let me know!
Regards,
Adam
> On 8 Jun 2022, at 08:50, VICTOR MANUEL ROMERO RODRIGUEZ <vi...@fintecheando.mx> wrote:
>
> Hello Fineract Community,
>
> TL;DR
> Consider the type of time that we want to solve using Fineract: actual event time, broadcast time, relative time, historical time, recurring time.
>
> UTC - is not silver bullet, but important for monetary transactions
> TimeZone - Required by regulations
> Future dates - Loan repayments and jobs
> Date format ISO 8601
>
> Now the reasoning and long explanation:
>
> I would like to remark that it is important to separate two concepts which are causing confusion.
>
> UTC is a constant defined a long time ago for clock synchronization and making fault-tolerant distributed real-time systems. It must be used for any enterprise class system and Apache Fineract is moving to be a framework for handling this as a native solution. It must be transparent for the end.
>
> The users of the Apache Fineract must be able to set Time Zones, Offsets (hey remember these are two different concepts too) and Date Format as per local regulation, political reasons, or any other. But all of them follow the Format ISO 8601 (aka ISO Date). with ISO 8601 values are ordered from the largest to smallest unit of time: year, month (or week), day, hour, minute, second, millisecond.
>
> For the challenges that we had to solve some years ago and still now is useful (now we are applying it to the Mifos Payment Hub connected to the Mexican Real Time 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 YYYY-MM-DD HH:mm:ss - trusted
> * Processing Date Time - UTC - Zone Id - Stored and processed as Timestamp milliseconds (8byte) - Display format ISO 8601 YYYY-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 YYYY-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 YYYY-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 YYYY-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 YYYY-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 YYYY-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 YYYY-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.
>
> 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, <sifiso@skyburgsystems.org <ma...@skyburgsystems.org>> 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 <adamsaghy@gmail.com <ma...@gmail.com>>
> Sent: Monday, 06 June 2022 10:25 AM
> To: dev@fineract.apache.org <ma...@fineract.apache.org>
> Cc: Ádám Sághy <adamsaghy@gmail.com <ma...@gmail.com>>
> 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, sifiso@skyburgsystems.org <ma...@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 <adamsaghy@gmail.com <ma...@gmail.com>>
> > Sent: Friday, 03 June 2022 9:32 AM
> > To: dev@fineract.apache.org <ma...@fineract.apache.org>
> > Cc: Ádám Sághy <adamsaghy@gmail.com <ma...@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
> >
>
>
Re: Timezone issues with Daylight savings
Posted by VICTOR MANUEL ROMERO RODRIGUEZ <vi...@fintecheando.mx>.
Hello Fineract Community,
TL;DR
Consider the type of time that we want to solve using Fineract: actual
event time, broadcast time, relative time, historical time, recurring time.
UTC - is not silver bullet, but important for monetary transactions
TimeZone - Required by regulations
Future dates - Loan repayments and jobs
Date format ISO 8601
Now the reasoning and long explanation:
I would like to remark that it is important to separate two concepts which
are causing confusion.
UTC is a constant defined a long time ago for clock synchronization and
making fault-tolerant distributed real-time systems. It must be used for
any enterprise class system and Apache Fineract is moving to be a framework
for handling this as a native solution. It must be transparent for the end.
The users of the Apache Fineract must be able to set Time Zones, Offsets
(hey remember these are two different concepts too) and Date Format as per
local regulation, political reasons, or any other. But all of them follow
the Format ISO 8601 (aka ISO Date). with ISO 8601 values are ordered from
the largest to smallest unit of time: year, month (or week), day, hour,
minute, second, millisecond.
For the challenges that we had to solve some years ago and still now is
useful (now we are applying it to the Mifos Payment Hub connected to the
Mexican Real Time 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 YYYY-MM-DD HH:mm:ss -
trusted
* Processing Date Time - UTC - Zone Id - Stored and processed
as Timestamp milliseconds (8byte) - Display format ISO 8601 YYYY-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 YYYY-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 YYYY-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 YYYY-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 YYYY-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 YYYY-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 YYYY-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.
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, <si...@skyburgsystems.org> 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 <ad...@gmail.com>
> Sent: Monday, 06 June 2022 10:25 AM
> To: dev@fineract.apache.org
> Cc: Ádám Sághy <ad...@gmail.com>
> 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, sifiso@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 <ad...@gmail.com>
> > Sent: Friday, 03 June 2022 9:32 AM
> > To: dev@fineract.apache.org
> > Cc: Ádám Sághy <ad...@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
> >
>
>
>
RE: Timezone issues with Daylight savings
Posted by si...@skyburgsystems.org.
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 <ad...@gmail.com>
Sent: Monday, 06 June 2022 10:25 AM
To: dev@fineract.apache.org
Cc: Ádám Sághy <ad...@gmail.com>
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, sifiso@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 <ad...@gmail.com>
> Sent: Friday, 03 June 2022 9:32 AM
> To: dev@fineract.apache.org
> Cc: Ádám Sághy <ad...@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
>
Re: Timezone issues with Daylight savings
Posted by Michael Telahun <mt...@trevi.et>.
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 <ad...@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, sifiso@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 <ad...@gmail.com>
>>> Sent: Friday, 03 June 2022 9:32 AM
>>> To: dev@fineract.apache.org
>>> Cc: Ádám Sághy <ad...@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
>>>
Re: Timezone issues with Daylight savings
Posted by James Dailey <ja...@gmail.com>.
Á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 <ad...@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, sifiso@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 <ad...@gmail.com>
> > Sent: Friday, 03 June 2022 9:32 AM
> > To: dev@fineract.apache.org
> > Cc: Ádám Sághy <ad...@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
> >
>
Re: Timezone issues with Daylight savings
Posted by Ádám Sághy <ad...@gmail.com>.
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, sifiso@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 <ad...@gmail.com>
> Sent: Friday, 03 June 2022 9:32 AM
> To: dev@fineract.apache.org
> Cc: Ádám Sághy <ad...@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
>
RE: Timezone issues with Daylight savings
Posted by si...@skyburgsystems.org.
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 <ad...@gmail.com>
Sent: Friday, 03 June 2022 9:32 AM
To: dev@fineract.apache.org
Cc: Ádám Sághy <ad...@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