Switch to new Java classes to manage dates

[lvca]

Discussed in #605

ArcadeDB uses java.util.Date to manage dates. Recent versions of Java provide more powerful classes to manage dates (Timestamp, LocalDatetime, etc) that also support granularity higher than millisecond.

[lvca]

In the new branch, we've introduced new methods in the Document:

  /**
   * Returns a java.util.Calendar object from a datetime property.
   *
   * @since 23.1.1
   */
  Calendar getCalendar(String propertyName);

  /**
   * Returns a java.time.LocalDate object from a date property.
   *
   * @since 23.1.1
   */
  LocalDate getLocalDate(String propertyName);

  /**
   * Returns a java.time.LocalDateTime object from a datetime property.
   *
   * @since 23.1.1
   */
  LocalDateTime getLocalDateTime(String propertyName);

  /**
   * Returns a java.time.ZonedDateTime object from a datetime property.
   *
   * @since 23.1.1
   */
  ZonedDateTime getZonedDateTime(String propertyName);

  /**
   * Returns a java.time.Instant object from a datetime property.
   *
   * @since 23.1.1
   */
  Instant getInstant(String propertyName);

[lvca]

Also, there are 3 new settings:

DATE_IMPLEMENTATION("arcadedb.dateImplementation",
      "Default date implementation to use on deserialization. By default java.util.Date is used, but the following are supported: java.util.Calendar, java.time.LocalDate",
      Class.class, java.util.Date.class),

DATE_TIME_IMPLEMENTATION("arcadedb.dateTimeImplementation",
      "Default datetime implementation to use on deserialization. By default java.util.Date is used, but the following are supported: java.util.Calendar, java.time.LocalDateTime, java.time.ZonedDateTime",
      Class.class, java.util.Date.class),

DATE_TIME_PRECISION("arcadedb.dateTimePrecision",
      "Precision to use with Datetime types on deserialization. Check the level of precision supported by your OS/JVM. By default is 'millisecond'. Supported are: 'millisecond', 'microsecond' and 'nanosecond'",
      String.class, "millisecond"),

You can use Java API to modify the settings, but better saving those settings in the database with the ALTER DATABASE SQL statement:

    database.command("sql", "alter database `arcadedb.dateTimeImplementation` `java.time.LocalDateTime`");
    database.command("sql", "alter database `arcadedb.dateImplementation` `java.time.LocalDate`");
    database.command("sql", "alter database `arcadedb.dateTimePrecision` nanosecond");

[lvca]

Once you set the precision (datetime only), ArcadeDB will use that setting to save the timestamp that supports that precision. With nanoseconds, it's a really long number, so bear this in mind. Longer number = more space used on disk and in RAM.

Since the datetime precision is a global setting (or database setting), the precision is not saved with each datetime property. This means if you change the precision after some of the date-time properties have been stored in your records, all your datetimes must be updated in the database.

For example, if you used nanoseconds:

ALTER DATABASE `arcadedb.dateTimePrecision` 'nanosecond'

And you had the property "signedOn" as DATETIME on a "Profile" vertex type, after a while, you understand you don't need that level of precision, you can change it back to milliseconds.

Run this query first to change the precision:

UPDATE Profile SET signedOn = signedOn.precision("millisecond")

Since the difference between nanoseconds (10E9) and milliseconds (10E3) is 10E6 = 1 million. The query above updates the date by dividing 1 million from the date (that is stored as a variable length number).

And now change the settings:

ALTER DATABASE `arcadedb.dateTimePrecision` 'millisecond'

[lvca]

I've also implemented property-level precision with

ALTER PROPERTY EventLog.date precision 'microsecond'

In this way, if you set a precision higher than milliseconds, and you haven't changed the default DateTime implementation )look above) from java.util.Date (still the default) to something better, then a java.time.LocalDateTime implementation will automatically be used.

For compatibility reasons, the default implementation for date and date-time is still java.util.Date, but if you're new to ArcadeDB, I suggest you set immediately using the new API with:

We will eventually switch the default implementation to the new API. Keep millisecond as precision if you don't need anything more fancier than that. Remember, the higher the precision, the larger space will be used on disk and RAM.

alter database `arcadedb.dateTimeImplementation` `java.time.LocalDateTime`
alter database `arcadedb.dateImplementation` `java.time.LocalDate`
alter database `arcadedb.dateTimePrecision` 'millisecond'

[lvca]

100% implemented. Docs are still pending.

[vic0824]

So there is still only one Datetime data type, but now there are new settings to specify the underlying implementation and the precision.
When you say "We will eventually switch the default implementation to the new API", does it mean that you will eliminate the two *Implementation settings and keep only the precision setting?
I was expecting this enhancement to be implemented by introducing new data types, but I can see your point about wasting disk space and RAM if you always store a LocalDateTime with maximum precision even when you don't need it.

I wonder what traditional relational DBMS do when they use the sql TIMESTAMP data type: do they always store it with nanoseconds precision or do they infer the precision from the literal values provided by the user? I guess it depends on the db internals, i.e. whether they store a fixed-size record in a page or not.
This sparked my curiosity so I had a look at the java.sql.Timestamp implementation (which has its well-known problems).

The apidoc says:

The precision of a Timestamp object is calculated to be either:

19 , which is the number of characters in yyyy-mm-dd hh:mm:ss
20 + s , which is the number of characters in the yyyy-mm-dd hh:mm:ss.[fff...] and s represents the scale of the given Timestamp, its fractional seconds precision.

but the source code of the valueOf(LocalDateTime dateTime) method (which is what I used in my project that uses Hsqldb) is:

    public static Timestamp valueOf(LocalDateTime dateTime) {
        return new Timestamp(dateTime.getYear() - 1900,
                             dateTime.getMonthValue() - 1,
                             dateTime.getDayOfMonth(),
                             dateTime.getHour(),
                             dateTime.getMinute(),
                             dateTime.getSecond(),
                             dateTime.getNano());
    }

so it's only possible to specify the accuracy when using the valueOf(String s) method, whereas the valueOf(LocalDateTime dateTime) method by default stores also nanoseconds.
But then again, java.sqlTimestamp holds a private int nanos; inside so it always uses the maximum space.

Perhaps an alternative solution would be to define different types, each one bound to a pre-defined precision, for example:

• Datetime: it has a resolution of 1 second
• DatetimeMillis: it has a resolution of 1 millisecond
• DatetimeMicros: it has a resolution of 1 microsecond
• DatetimeNanos: it has a resolution of 1 nanosecond

This would allow to engine to allocate only the minimum required space for each data type, and if it's implemented using the new API, you could even create more types for cases when you only need the date and not the time, thus further optimizing the resources.
In this way, there would be no need for an additional setting to configure the precision, but it would lead to a proliferation of types, so there is no clear winner, it is more a matter of personal preference.

If you go along the route of the precision setting, you could even allow setting it to "seconds", "minutes", "hours", "days", to optimize the space allocated even further.

[lvca]

The getNano() in LocalDateTime returns the underlying nano integer that stores the fractional part of the second. If you have millisecond accuracy, you'll have 333000000, with microseconds it would be 333333000 and with nanos would be 333333333. In this way you can use one variable to store millis, micros and nanos. The same have done with java.sql.Timestamp.

With ArcadeDB all the dates types become a single number. Since ArcadeDB already compresses numbers by using only the bytes required (it's a variable number, 1 byte only for the range 0-128) it's already ok for storing any big number. So it was much easier to convert all the date-time types into numbers. The missing information is the level of accuracy. it would be much easier to store an additional property that tells the accuracy of the date, like N:3473673647346 that tells us it's in (N) nanoseconds, but it would break the current implementation. Not a big deal if we'd use new datatypes as you suggested.

Also, you can have nanosecond accuracy, but you want to work with ZonedDateTime instead of LocalDateTime. Or also Instant. In Java now there are multiple implementations for dates, so we'd need to let ArcadeDB which implementation the user is comfortable with and we get and return that class every time without letting the user convert it into its date class.

So I like your idea about using new types for dates. Since >99% of people will be ok with milliseconds precision and we don't want to break things unless it's fundamental to the progress, what about introducing 3 new types?

• DATETIME_MICROS for microseconds
• DATETIME_NANOS for nanoseconds
• DATETIME_SECONDS for just seconds. I believe tons of people should use this when they manage date time types, but we can't make it the default without breaking existent databases and code

In this way, the current DATETIME would work with milliseconds without breaking the compatibility with the past.

Also, I could get rid of the new precision attribute in Properties. You don't need to set the precision anymore, just different date types.

Is anybody cool with that? WDYT @gramian? In this case I need a few more days to finalize such changes before 23.1.1 and docs updated.