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