如何为使用日期的BIGINT类型计数每天行数的HSQLDB形成SQL查询？

Question

我有一个HSQLDB，版本。1.8.0，带有示例架构的数据库，如下所示：

CREATE CACHED TABLE ENTRY
(ENTRYID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
 REQID VARCHAR,
 REVOCATIONDATE BIGINT)

我想检索每个日期的行计数，如下所示：

ENTRYID DATE_COUNT REVOCATIONDATE

1 10 2014-01-01  
2 5 2014-01-02  
3 15 2014-01-03

问题在于REVOCATIONDATE是一个BIGINT，而不是正常的日期或时间戳。这是供应商提供的数据库，因此，假设不允许更改架构。

如何创建执行此操作的SQL查询？

Answer 1

简单的答案是HSQLDB 1.8是一个旧版本，不支持某些高级功能。HSQLDB 2.3.x支持各种功能，可以在不同的表示形式之间进行转换。

使用HSQLDB
1.8，可以在Java中创建自己的函数，以将Timestamp的毫秒值转换为java.sql.Timestamp或java.sql.Date

在您创建的类中类似这样的内容：

static java.sql.Date millisToDate(long millis) {
    return new java.sql.Date(millis);
}

如果随后将此类包含在用于运行数据库的类路径中，则可以像下面这样在SELECT语句中调用该函数：

SELECT ENTRYID, "myclass.millisToDate"(REVOCATIONDATE) FROM ENTRY

您的结果示例将如下所示：

SELECT MIN(ENTRYID), COUNT(ENTRYID), "myclass.millisToDate"(REVOCATIONDATE) FROM ENTRY
    GROUP BY "myclass.millisToDate"(REVOCATIONDATE)

我实际上没有尝试过上述任何建议，但是它们应该可以工作，也许需要进行一些改动。