如何形成一个HSQLDB的SQL查询,它使用日期的BIGINT类型来计算每天的行数? [英] How can I form a SQL Query for a HSQLDB that counts the # of rows per day using a BIGINT type for a date?
问题描述
CREATE CACHED TABLE ENTRY
(ENTRYID INTEGER GENERATED按默认值(从0开始)NOT NULL主键,
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查询将这样做?
简单的答案是HSQLDB 1.8是一个旧版本,不支持一些高级功能。 HSQLDB 2.3.x支持全方位的功能,可在不同的表示之间进行转换。
使用HSQLDB 1.8可以在Java中创建自己的函数,以将对于java.sql.Timestamp或java.sql.Date的Timestamp
您创建的类中的内容类似于
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 BYmyclass.millisToDate(REVOCATIONDATE)
我没有实际上尝试过任何上述建议,但是他们应该工作,也许有一些改变。
I have an HSQLDB, ver. 1.8.0, database with a sample schema with something like this:
CREATE CACHED TABLE ENTRY
(ENTRYID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
REQID VARCHAR,
REVOCATIONDATE BIGINT)
I would like to retrieve a row count for every date, something like this:
ENTRYID DATE_COUNT REVOCATIONDATE 1 10 2014-01-01 2 5 2014-01-02 3 15 2014-01-03
The problem is that the REVOCATIONDATE is a BIGINT instead of a normal date or timestamp. This is a vendor provided DB so, assume the schema is not allowed to be changed.
How can I create a SQL query that will do this?
The simple answer is HSQLDB 1.8 is an old version and does not support some advanced functions. HSQLDB 2.3.x supports a full range of functions to convert between different representations.
With HSQLDB 1.8 it is possible to create your own function in Java to convert the millisecond value of a Timestamp to a java.sql.Timestamp or java.sql.Date
Something like this in a class that you create:
static java.sql.Date millisToDate(long millis) {
return new java.sql.Date(millis);
}
If you then include this class on the classpath used for running the database, you can call the function in your SELECT statement like this:
SELECT ENTRYID, "myclass.millisToDate"(REVOCATIONDATE) FROM ENTRY
Your result example will look like this:
SELECT MIN(ENTRYID), COUNT(ENTRYID), "myclass.millisToDate"(REVOCATIONDATE) FROM ENTRY
GROUP BY "myclass.millisToDate"(REVOCATIONDATE)
I haven't actually tried any of the above suggestions, but they should work, perhaps with some alteration.
这篇关于如何形成一个HSQLDB的SQL查询,它使用日期的BIGINT类型来计算每天的行数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!