Bijection between client- and sever-side date/time formats

[01es]

Description

Background

In most cases formatting of dates at the client- and server-sides is independent and may follow different formats. Date/time values are marshalled between the client- and server-sides as the number of milliseconds (long).

However, there are cases where date/time values are transmitted as formatted strings. This happens, for example, when a property of type Date represents one of the composite key members for an entity that needs to be autocompleted. Autocompleters operate strictly with string representations and try to match/find an entity instance based on a string representation of it key, where all key members are encoded.

String representation of date/time values at the client side are different to those used at the server-side. The main objective of this issue is to align every aspect of the platform where string representations of date/time values could be used to following the same, domain-specific formatting rules.

Requirements

This issue covers the work required to rectify the differences between different time formats at different application layers -- JS at the client, Java at the application server and its communication with the databases.

The following items outline the scope of work:

• [ ] 1. Map date and time formats (separately) between JS, Java and SQL for supported RDBMSes (SQL Server and PostgreSQL).

• [ ] 2. Need to introduce a predefined set of date and time formats to be supported. This is only reasonable taking into account that each format needs special support at the client-side for approximations as well as mapping between different app layers.

• [ ] 3. IDates should drive formatting, format mapping and setting of an active format for an application. As a note, this issue is not concerned with time-zone specific transformations, a separate issue would need to be created for that when and if required.

• [ ] 4. IDates need to be incorporated into EntityUtils and DynamicEntityKey to be used in respective methods toString when converting Date values to string based on domain-specific formats, configured at an application level.

  Implementation remark: A consideration should be given to using Guice's ability to inject static variables. If this approached is considered suitable then requestStaticInjection(EntityUtils.class); and requestStaticInjection(DynamicEntityKey.class); would need to be added to BasicWebServerModule, with fields @Inject private static IDates dates; added to both EntityUtils and DynamicEntityKey.

• [ ] 5. EQL3 AbstractFunction3 is responsible for converting instances of ISingleOperand3 to string and provide special handling for dates. This class needs to be enhanced to accept IDates and implement conversion of dates in accordance with mappings between date and time formats. SQL Server conversion should used function FORMAT instead of CONVERT. Class AbstractFunction3 is used in Concat3.sql.

• [ ] 7. Formulae for converting date/time to string should take into account special cases of the ways the time portion is handled at the client-side. Specifically, if milliseconds are 000 and seconds are 00 then only hours and minutes are included, and if milliseconds are 000 and seconds are non-00 then only seconds are included (see also #2093). There needs to be a case/when clause that would determine whether seconds and millisecond need to be included into the conversion based on the actual date/time column values at the database level. Each supported RDBMS has their own functions for obtaining seconds and millisecond from date/time values (e.g. PostgreSQL has function extract, SQL server -- function DATEPART).

  2023-09-05: PostgreSQL support has been implemented. Refer af3f5df1f3c62217053957037965a07744148fc7. 2023-10-17: PostgreSQL support was updated and SQL Server support implemented. Refer 4db067405c12580b10ea819509ac0f9f9afbcc6f.

  SQL Server:

  declare @dt datetime2 = '2019-12-31 01:00.00.100'
  select case
         when DATEPART(hour, @dt) = 0 and DATEPART(minute, @dt) = 0 and DATEPART(second, @dt) = 0 and DATEPART(millisecond, @dt) = 0 then FORMAT(@dt, 'dd/MM/yyyy') 
         when DATEPART(second, @dt) = 0 and DATEPART(millisecond, @dt) = 0 then FORMAT(@dt, 'dd/MM/yyyy HH:mm')
         when DATEPART(millisecond, @dt) > 0 then FORMAT(@dt, 'dd/MM/yyyy HH:mm:ss.fff')
         else FORMAT(@dt, 'dd/MM/yyyy HH:mm:ss') end
  

  PostgreSQL:

  with vals as (
      select '2019-12-30 00:00:00.001'::timestamp as dt
  )
  select case 
         when extract(milliseconds from dt) = 0 and extract(minutes from dt) = 0 and extract(hours from dt) = 0 then to_char(dt, 'DD/MM/YYYY') 
         when extract(milliseconds from dt) = 0 then to_char(dt, 'DD/MM/YYYY HH24:MI') 
         when cast(floor(extract(milliseconds from dt)) as integer) - 1000 * cast(floor(extract(seconds from dt)) as integer) > 0 then to_char(dt, 'DD/MM/YYYY HH24:MI:SS.MS') 
         else to_char(dt, 'DD/MM/YYYY HH24:MI:SS') end
  from vals
  

• [ ] 8. Consider supporting @DateOnly and @TimeOnly semantics. For example, consider entity Timesheet with composite key members person: Person, date: Date @DateOnly and startTime: Date @TimeOnly. With a space as key member separator, date format DD/MM/YYYY and time format HH:mm:ss.SSS, the string representation for entity {person: "01es", date: "2023-09-05", startTime: "10:42:05.000"} would be 01es 05/09/2023 10:42:05. And so the formatting logic would need to take into account the @DateOnly and @TimeOnly to correctly represent such date/time values as strings.

  ~An important consideration for this item, is the ability for ISingleOperand3 in EQL3 to carry the necessary information, namely – annotations @DateOnly and @TimeOnly.~

  Issue https://github.com/fieldenms/tg/issues/2115 and this one should be considered together as it introduces Java types LocatDate, LocateDateTime and LocalTime, which make @DateOnly and @TimeOnly irrelevant.

Expected outcome

Support for consistent date/time formatting across different architectural layers. Ability to autocomplete entities with date/time key members.