Storing DateTime values with microsecond precision

[vic0824]

The DATETIME type uses millisecond precision (I guess because underneath it uses the java.util.Date class).
I think it would be very useful to have a type that allows representing date-time values with microsecond precision (actually, if a change is done, it might as well support nanoseconds).
This is a feature supported by many databases, and having it in ArcadeDB would help in establishing ArcadeDB as a valid alternative for many use cases.
I'd like to try some workarounds to represent microseconds, I'm not sure which is better (or less bad):
1 - store the value as an ISO-8601 string representation with microseconds, e.g. '2011-12-03T10:15:30.388724'; I'm not sure if the db engine will know how to sort and compare values in this format, i.e. if it will know how to evaluate a WHERE clause containing "time1 < time2".
2 - use two fields to store the value: a DATETIME field that stores the datetime with a millisecond resolution, and a SHORT (smallest primitive that allows representing the maximum number of microseconds in a millisecond);
3 - use BigDecimal and design my own representation of a DateTime as a number of microseconds since 1/1/1970 00:00:00.000000

Comments and suggestions are welcome.

[lvca]

Currently the best way to have microseconds is to save it as a string, and work with the date format like you mentioned. I've checked our SQL methods to convert dates from strings and we improve the usage for .asDateTime() by supporting a custom format as an optional parameter.

So I just implemented it. Now you can do:

results = db.query("sql", "select dateAsString.asDate(\"yyyy-MM-dd'T'HH:mm:ss.SSSSSS\") as convert from TestConversion");

Where the content of dateAsString property is '2011-12-03T10:15:30.388724'.

This is already in the 22.11.1-SNAPSHOT and will be released this month in the official 22.11.1 release.

Commit: 14a1260

[gramian]

What does this mean in terms of (minimal) Java version?

[lvca]

The new Date framework was released with Java 1.8 (pretty old), but we already suggested Java11, so no changes in this.

[gfinger]

Is there a target release when LocalDates from the java.time package will be accepted types in the Java API of ArcadeDB?

[lvca]

We have a branch that supports LocalDate, LocalDateTime, ZonedDate, and Instant. Still, some details need to be tested to be merged. It could be out this week or the next.

[lvca]

The branch is online and under review. To follow this issue check out #608.

[lvca]

Fixed in #608.