比较Derby中的时间戳和日期 [英] comparing timestamp and date in Derby
问题描述
我需要比较SELECT查询中的日期和时间戳。通常,大多数其他数据库平台(MySQL,MSSQL,Oracle等)都支持此功能,但在Derby中会引发以下错误:
I need to compare a date and a timestamp in a SELECT query. Normally, this is supported by most other DB platforms(MySQL, MSSQL, Oracle, etc.) but in Derby it throws this error:
原因:java.sql.SQLSyntaxErrorException:不支持
'DATE'和'TIMESTAMP'之间的比较。类型必须是可比较的。
字符串类型还必须具有匹配的排序规则。如果排序规则与
不匹配,则可能的解决方案是强制转换操作数以使其强制为
默认排序规则(例如,在sys.systables中选择表名,其中
CAST(表名为VARCHAR(128))) ='T1')
Caused by: java.sql.SQLSyntaxErrorException: Comparisons between 'DATE' and 'TIMESTAMP' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
我不想使用CAST,因为我无法更改SQL查询。这是Derby的bug,将在某个时间得到修复吗?我当前正在使用Derby 10.8.2.2。
I don't want to use CASTs because I cannot change the SQL query. Is this a bug from Derby that will get fixed sometime? I'm currently using Derby 10.8.2.2.
谢谢!
推荐答案
如果您是从Java程序进行连接,则可以使用各种getxxx()方法(实际上是从Java.util.date继承的)获取时间戳的特定部分
If you are connecting from a java program you can get specific parts of a Timestamp using various getxxx() methods (these are actually inherited from Java.util.date)
请参阅文档 http://docs.oracle.com /javase/7/docs/api/java/util/Date.html
并检查getDate(),getDay(),getHour()。 ..方法
and check the getDate(), getDay() getHour().... methods
这篇关于比较Derby中的时间戳和日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!