SQL: toDate and toDateTime should get custom format

[lvca]

Discussed in #605

^{Originally posted by vic0824 November 8, 2022}
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

Updated docs: ArcadeData/arcadedb-docs@a2c21b8