Search code examples
javajdbchsqldb

HSQLDB 2.4 How to use UNIX_MILLIS() as default value for BIGINT column

I run the next SQL (JDBC)

CREATE TABLE IF NOT EXISTS PUBLIC.MY_DATA(
ID BIGINT IDENTITY PRIMARY KEY NOT NULL,
...,
LAST_MODIFIED BIGINT DEFAULT UNIX_MILLIS() NOT NULL)

and get SQLSyntaxErrorException: unexpected token: UNIX_MILLIS

But according to the documentation

UNIX_MILLIS ( [ ] )

This function returns a BIGINT value. With no parameter, it returns the number of milliseconds since 1970-01-01. With a DATE or TIMESTAMP parameter, it converts the argument into number of milliseconds since 1970-01-01. (HyperSQL)

Any help is appreciated

Solution

  • Looking at the definition of the DEFAULT clause (in table creation), and the fact that what is allowed in there is limited to

    <default option> ::= <literal> | <datetime value function> | USER
| CURRENT_USER | CURRENT_ROLE | SESSION_USER | SYSTEM_USER |
CURRENT_CATALOG | CURRENT_SCHEMA | CURRENT_PATH | NULL

    And <datetime value function> is defined as

    datetime value function

    <datetime value function> ::= ...

    Specify a function that returns a datetime value. The supported datetime value functions are listed and described in the Built In Functions chapter.

    As UNIX_MILLIS does not return a datetime value, but a BIGINT, it is entirely possible that UNIX_MILLIS is not considered a <datetime value function>, and therefor not available in the DEFAULT clause.

    This seems to be supported by looking at the parser of the default clause, which filters allowed expressions based on their result type.

    As a tip, I haven't tested it, but it is possible that enabling PostgreSQL compatibility mode might allow you to use DEFAULT UNIX_TIME() or maybe DEFAULT (UNIX_TIME()).