Creating date objects with the date() function

[docbacardi]

ArcadeDB Version:

ArcadeDB Server v23.9.1-SNAPSHOT (build 54c7e798b004f5d80e9fe5e3ce4afd7d89e50aea/1693919158748/main)

OS and JDK Version:

Running on Linux 5.15.0-78-generic - OpenJDK 64-Bit Server VM 11.0.20.1

Expected behavior

The SQL command "date()" should create a date object as described here: https://docs.arcadedb.com/#_date .

Actual behavior

The "date()" function returns null or throws an exception.

Steps to reproduce

All commands were tested in the studio and with a psql client. The examples here show only the psql output. Running the commands in the studio gives the same results.

The function "sysdate()" works as expected:

testdatabase=> SELECT sysdate();
           sysdate()           
-------------------------------
 Wed Sep 06 16:56:01 CEST 2023
(1 row)

testdatabase=> SELECT sysdate().asLong();
 sysdate().asLong() 
--------------------
      1694012168887
(1 row)

The function "date()" throws an exception. This is based on the example shown here: https://docs.arcadedb.com/#_date

testdatabase=> SELECT date('2012-07-02', 'yyyy-MM-dd');
ERROR:  Error on executing query: Text '2012-07-02' could not be parsed: Unable to obtain LocalDateTime from TemporalAccessor: {},ISO,Europe/Berlin resolved to 2012-07-02 of type java.time.format.Parsed

And in the server logs:

Sep 06 14:52:30 kuhbe01.hilscher.local run.sh[45242]: java.time.format.DateTimeParseException: Text '2012-07-02' could not be parsed: Unable to obtain LocalDateTime from TemporalAccessor: {},ISO,Europe/Berlin r>
Sep 06 14:52:30 kuhbe01.hilscher.local run.sh[45242]:         at java.base/java.time.format.DateTimeFormatter.createError(DateTimeFormatter.java:2017)
Sep 06 14:52:30 kuhbe01.hilscher.local run.sh[45242]:         at java.base/java.time.format.DateTimeFormatter.parse(DateTimeFormatter.java:1952)
Sep 06 14:52:30 kuhbe01.hilscher.local run.sh[45242]:         at java.base/java.time.LocalDateTime.parse(LocalDateTime.java:492)
Sep 06 14:52:30 kuhbe01.hilscher.local run.sh[45242]:         at com.arcadedb.query.sql.function.time.SQLFunctionDate.execute(SQLFunctionDate.java:77)
Sep 06 14:52:30 kuhbe01.hilscher.local run.sh[45242]:         at com.arcadedb.query.sql.parser.FunctionCall.execute(FunctionCall.java:130)
Sep 06 14:52:30 kuhbe01.hilscher.local run.sh[45242]:         at com.arcadedb.query.sql.parser.FunctionCall.execute(FunctionCall.java:85)
Sep 06 14:52:30 kuhbe01.hilscher.local run.sh[45242]:         at com.arcadedb.query.sql.parser.LevelZeroIdentifier.execute(LevelZeroIdentifier.java:65)
Sep 06 14:52:30 kuhbe01.hilscher.local run.sh[45242]:         at com.arcadedb.query.sql.parser.BaseIdentifier.execute(BaseIdentifier.java:66)
Sep 06 14:52:30 kuhbe01.hilscher.local run.sh[45242]:         at com.arcadedb.query.sql.parser.BaseExpression.execute(BaseExpression.java:145)
Sep 06 14:52:30 kuhbe01.hilscher.local run.sh[45242]:         at com.arcadedb.query.sql.parser.Expression.execute(Expression.java:87)
Sep 06 14:52:30 kuhbe01.hilscher.local run.sh[45242]:         at com.arcadedb.query.sql.parser.ProjectionItem.execute(ProjectionItem.java:137)
Sep 06 14:52:30 kuhbe01.hilscher.local run.sh[45242]:         at com.arcadedb.query.sql.parser.Projection.calculateSingle(Projection.java:129)
Sep 06 14:52:30 kuhbe01.hilscher.local run.sh[45242]:         at com.arcadedb.query.sql.executor.ProjectionCalculationStep.calculateProjections(ProjectionCalculationStep.java:66)
Sep 06 14:52:30 kuhbe01.hilscher.local run.sh[45242]:         at com.arcadedb.query.sql.executor.ProjectionCalculationStep$1.next(ProjectionCalculationStep.java:51)
Sep 06 14:52:30 kuhbe01.hilscher.local run.sh[45242]:         at com.arcadedb.query.sql.parser.LocalResultSet.next(LocalResultSet.java:72)
Sep 06 14:52:30 kuhbe01.hilscher.local run.sh[45242]:         at com.arcadedb.server.http.handler.AbstractQueryHandler.serializeResultSet(AbstractQueryHandler.java:94)
Sep 06 14:52:30 kuhbe01.hilscher.local run.sh[45242]:         at com.arcadedb.server.http.handler.PostCommandHandler.execute(PostCommandHandler.java:95)
Sep 06 14:52:30 kuhbe01.hilscher.local run.sh[45242]:         at com.arcadedb.server.http.handler.DatabaseAbstractHandler.execute(DatabaseAbstractHandler.java:99)
Sep 06 14:52:30 kuhbe01.hilscher.local run.sh[45242]:         at com.arcadedb.server.http.handler.AbstractHandler.handleRequest(AbstractHandler.java:126)
Sep 06 14:52:30 kuhbe01.hilscher.local run.sh[45242]:         at io.undertow.server.Connectors.executeRootHandler(Connectors.java:393)
Sep 06 14:52:30 kuhbe01.hilscher.local run.sh[45242]:         at io.undertow.server.HttpServerExchange$1.run(HttpServerExchange.java:859)
Sep 06 14:52:30 kuhbe01.hilscher.local run.sh[45242]:         at org.jboss.threads.ContextHandler$1.runWith(ContextHandler.java:18)
Sep 06 14:52:30 kuhbe01.hilscher.local run.sh[45242]:         at org.jboss.threads.EnhancedQueueExecutor$Task.run(EnhancedQueueExecutor.java:2513)
Sep 06 14:52:30 kuhbe01.hilscher.local run.sh[45242]:         at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1538)
Sep 06 14:52:30 kuhbe01.hilscher.local run.sh[45242]:         at org.xnio.XnioWorker$WorkerThreadFactory$1$1.run(XnioWorker.java:1282)
Sep 06 14:52:30 kuhbe01.hilscher.local run.sh[45242]:         at java.base/java.lang.Thread.run(Thread.java:829)
Sep 06 14:52:30 kuhbe01.hilscher.local run.sh[45242]: Caused by: java.time.DateTimeException: Unable to obtain LocalDateTime from TemporalAccessor: {},ISO,Europe/Berlin resolved to 2012-07-02 of type java.time.>
Sep 06 14:52:30 kuhbe01.hilscher.local run.sh[45242]:         at java.base/java.time.LocalDateTime.from(LocalDateTime.java:461)
Sep 06 14:52:30 kuhbe01.hilscher.local run.sh[45242]:         at java.base/java.time.format.Parsed.query(Parsed.java:235)
Sep 06 14:52:30 kuhbe01.hilscher.local run.sh[45242]:         at java.base/java.time.format.DateTimeFormatter.parse(DateTimeFormatter.java:1948)
Sep 06 14:52:30 kuhbe01.hilscher.local run.sh[45242]:         ... 24 more
Sep 06 14:52:30 kuhbe01.hilscher.local run.sh[45242]: Caused by: java.time.DateTimeException: Unable to obtain LocalTime from TemporalAccessor: {},ISO,Europe/Berlin resolved to 2012-07-02 of type java.time.form>
Sep 06 14:52:30 kuhbe01.hilscher.local run.sh[45242]:         at java.base/java.time.LocalTime.from(LocalTime.java:431)
Sep 06 14:52:30 kuhbe01.hilscher.local run.sh[45242]:         at java.base/java.time.LocalDateTime.from(LocalDateTime.java:457)
Sep 06 14:52:30 kuhbe01.hilscher.local run.sh[45242]:         ... 26 more

Trying a different format gives a null result (which is only visible in the studio as <null>):

testdatabase=> SELECT date("Wed Sep 06 16:35:27 CEST 2023");
--
(1 row)

[docbacardi]

Looks like I stumbled over 2 problems. The first one is using the SQL function date() without a time component. This is suggested by the example from here:

SELECT FROM Account WHERE created <= date('2012-07-02', 'yyyy-MM-dd')

While a complete date + time works, just a date throws the exception Unable to obtain LocalTime from TemporalAccessor:

testdatabase=> SELECT date('2012-07-02 10:03:21');
  date('2012-07-02 10:03:21')  
-------------------------------
 Mon Jul 02 12:03:21 CEST 2012
(1 row)

testdatabase=> SELECT date('2012-07-02');
ERROR:  Error on executing query: Text '2012-07-02' could not be parsed: Unable to obtain LocalDateTime from TemporalAccessor: {},ISO,Europe/Berlin resolved to 2012-07-02 of type java.time.format.Parsed
testdatabase=> SELECT date('2012-07-02', 'yyyy-MM-dd');
ERROR:  Error on executing query: Text '2012-07-02' could not be parsed: Unable to obtain LocalDateTime from TemporalAccessor: {},ISO,Europe/Berlin resolved to 2012-07-02 of type java.time.format.Parsed

This could be fixed by providing defaults for the time fields, like #1256 does.

The second problem are missing error messages for an invalid date format like this:

testdatabase=> SELECT date("Wed Sep 06 16:35:27 CEST 2023");
--
(1 row)

Currently a null is returned.

[lvca]

Thanks @docbacardi for the PR!