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, <[email protected]> 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 <[email protected]> > Sent: Monday, 06 June 2022 10:25 AM > To: [email protected] > Cc: Ádám Sághy <[email protected]> > 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, [email protected] wrote: > > > > Hi Adam, > > > > Thank you for sharing. Just wanted to know what the impact of having a > > server located in a different continent to the user would be? Using > > this approach will it pickup the user device's date settings > automatically? > > > > > > > > > > -----Original Message----- > > From: Ádám Sághy <[email protected]> > > Sent: Friday, 03 June 2022 9:32 AM > > To: [email protected] > > Cc: Ádám Sághy <[email protected]> > > Subject: Timezone issues with Daylight savings > > > > Dear Community, > > > > I was spending some time to understand in detail the date handling of > > Fineract and i might learnt a gap which could be a potential problem > > when the tenant (or system) timezone has daylight savings feature. > > > > Current behaviour: > > - Some of the audit datetime fields are using system timezone (usually > > 3rd party libs, like: quartz) > > - Some of the audit datetime fields are using tenant timezone (usually > > the fineract audit features, like: creation date, last modified date) > > - We are storing them in DB without timezone attribute > > > > The problem: > > - If a transaction (#1) was done at 2:59 AM 30th of October and 1 > > minutes later we are adjusting the clock backward with an hour and the > > following incoming a new transaction (#2) then the creation date will > > be 2:02 AM 30th of October > > > > This potentially a huge problem if any logic is depending on the > > creation date or using it for audit purposes. > > > > I would like to propose the following solution: > > > > - We should introduce Timezone aware datetime handling into Fineract > > and also store the timezone attribute for these kind of date in the > > database as well > > > > Should you have any question, please let me know! > > > > Regards, > > Adam > > > > >
