A subtraction of two dates leads to exception

Help
2011-02-23
2014-01-19
  • Dmitry Katsubo

    Dmitry Katsubo - 2011-02-23

    Dear HSQLDB community,

    I am trying to compose the SQL query, which would be valid in terms of Hibernate HQL and MySQL.

    Given the following table definition

    create table document
    (
        id                      integer unsigned generated always as identity,
        image_indexing_date     datetime,
        constraint document_pk primary key (id)
    );
    

    I would like to execute the following query:

    select count(*) from document where current_date - cast(image_indexing_date as date)) day >= 20
    

    and I get the following exception:

    org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; SQL [select count(*) from document where current_date - cast(image_indexing_date as date) >= 20]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query
        at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:629)
        at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)
        at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:411)
        at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374)
        at org.springframework.orm.hibernate3.HibernateTemplate.findByNamedQuery(HibernateTemplate.java:979)
        at org.springframework.orm.hibernate3.HibernateTemplate.findByNamedQuery(HibernateTemplate.java:971)
        ...
    Caused by: org.hsqldb.HsqlException: incompatible data types in combination
        at org.hsqldb.error.Error.error(Error.java:131)
        at org.hsqldb.error.Error.error(Error.java:101)
        at org.hsqldb.types.DateTimeType.getCombinedType(DateTimeType.java:355)
        at org.hsqldb.ExpressionArithmetic.resolveTypesForArithmetic(ExpressionArithmetic.java:375)
        at org.hsqldb.ExpressionArithmetic.resolveTypes(ExpressionArithmetic.java:300)
        at org.hsqldb.ExpressionLogical.resolveTypes(ExpressionLogical.java:597)
        at org.hsqldb.QuerySpecification.resolveExpressionTypes(QuerySpecification.java:705)
        at org.hsqldb.QuerySpecification.resolveTypesPartOne(QuerySpecification.java:818)
        at org.hsqldb.QueryExpression.resolve(QueryExpression.java:197)
        at org.hsqldb.ParserDQL.compileCursorSpecification(ParserDQL.java:5477)
        at org.hsqldb.ParserCommand.compilePart(ParserCommand.java:133)
        at org.hsqldb.ParserCommand.compileStatement(ParserCommand.java:63)
        at org.hsqldb.Session.compileStatement(Session.java:906)
        at org.hsqldb.StatementManager.compile(StatementManager.java:335)
        at org.hsqldb.Session.execute(Session.java:1009)
        at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(JDBCPreparedStatement.java:3882)
        ... 53 more
    

    I have tried also the following query:

    select count(*) from document where cast(image_indexing_date as date) between current_date - 20 and current_date
    

    also with no success. It looks like I have to use intervals, like in this post:

    select count(*) from document where (current_date - cast(image_indexing_date as date)) day >= 20
    

    The last query is executed OK, but cannot be parsed by Hibernate HSQL parser (BinaryArithmeticOperatorNode evaluates the subtraction of two dates to double but does not support intervals).

    HSQLDB v2.0 was throwing a different exception:

    Caused by: org.hsqldb.HsqlException: interval qualifier missing
        at org.hsqldb.error.Error.error(Error.java:131)
        at org.hsqldb.error.Error.error(Error.java:101)
        at org.hsqldb.ExpressionArithmetic.resolveTypesForArithmetic(ExpressionArithmetic.java:331)
        at org.hsqldb.ExpressionArithmetic.resolveTypes(ExpressionArithmetic.java:298)
    

    but it looks like the subtraction of datetime datatypes was changed between 2.0-2.1 (looking at this diff) although does not help.

    Any suggestions are welcomed.

     
  • Dmitry Katsubo

    Dmitry Katsubo - 2011-02-23

    Ops, the first query should read:

    select count(*) from document where current_date - cast(image_indexing_date as date) >= 20
    

    Sorry, can't edit the original post.

     
  • Fred Toussi

    Fred Toussi - 2011-02-23

    This query currently works:

    select count(*) from document where (current_TIMESTAMP - image_indexing_date) day >= 20

    There must be a way in hibernate to pass the query as the original SQL. Alternatively use the TIMESTAMPDIFF function.
    I will check the alternative forms you mentioned.

     
  • Dmitry Katsubo

    Dmitry Katsubo - 2011-02-24

    Yes, there is a possibility to pass a query as raw SQL query into Hibernate, but then I loose portability: if the query is passed as HQL (see chapter 14.10), then current_timestamp() function is translated to underlying DB dialect that is supported (e.g. for MySQL is would be current_timestamp() but for DB2 just current timestamp). That is why it would be fantastic, if I can write the query in a way, that is compatible for different databases. For example, MySQL does not accept " day" (perhaps, does not support datetime intervals?), but for DB2 it is OK.

     
  • Fred Toussi

    Fred Toussi - 2011-02-24

    MySQL is not compatible with the SQL Standard in several areas, including this. DB2 is generally compatible, so is HSQLDB.

    The query with BETWEEN should be written as

    select count(*) from document where cast(image_indexing_date as date) between current_date - 20 DAY and current_date
    
     
  • Fred Toussi

    Fred Toussi - 2011-04-03

    The latest, post 2.1 version supports your query and the one with interval qualifier:

    select count(*) from document where current_date - cast(image_indexing_date as date) >= 20
    select count(*) from document where (current_date - cast(image_indexing_date as date)) day >= 20
    
     
  • Dmitry Katsubo

    Dmitry Katsubo - 2011-09-01

    fredt, thank you for your comments. I am still trying to compose SQL that will work with both HSQL and MySQL.
    For example, the following syntax is supported by MySQL

    select count(*) from document where cast(image_indexing_date as date) >= current_date -  interval 20 day
    

    but not in HSQLDB, which supports this:

    select count(*) from document where cast(image_indexing_date as date) >= current_date -  20 day
    

    The only difference is "interval" keyword. I believe that HSQL allows "interval" keyword only in combination with "to", however I can't infer this from the grammar. Is it possible for HSQL to support this syntax as well? If not, you know can mention the paragraph+page number in SQL'92 standard which is violated, so I can create a bug for MySQL?

    Thanks.

     
  • Fred Toussi

    Fred Toussi - 2011-09-01

    This is already supported. Does it work with MySQL?

    select count(*) from document where cast(image_indexing_date as date) >= current_date - interval '20' day

     
  • Dmitry Katsubo

    Dmitry Katsubo - 2011-09-02

    Yes, this query works in MySQL! Thanks!

    I think very minor problem left: I can't parametrize this query. More exactly:

    org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; SQL [select count(*) from document where cast(text_indexing_date as date) >= current_date - interval ? day]
    ...
    Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: INTERVAL
        at org.hsqldb.error.Error.error(Error.java:81)
        at org.hsqldb.error.Error.error(Error.java:70)
        at org.hsqldb.ExpressionColumn.checkColumnsResolved(ExpressionColumn.java:862)
        at org.hsqldb.QueryExpression.resolve(QueryExpression.java:193)
        at org.hsqldb.ParserDQL.compileCursorSpecification(ParserDQL.java:5950)
        at org.hsqldb.ParserCommand.compilePart(ParserCommand.java:145)
        at org.hsqldb.ParserCommand.compileStatement(ParserCommand.java:64)
        at org.hsqldb.Session.compileStatement(Session.java:925)
        at org.hsqldb.StatementManager.compile(StatementManager.java:350)
        at org.hsqldb.Session.execute(Session.java:1028)
        at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(JDBCPreparedStatement.java:3890)
        ... 54 more
    

    Later I can bind the parameter to string value '20' (or maybe HSQL can be smart enough and handle the case if bind it to string 20?)

    As far as I know, parameters cannot be put into quotes, e.g. '?' because then they are interpreted as normal strings.

     
  • Fred Toussi

    Fred Toussi - 2011-09-02

    This is done with a cast:

    select count(*) from document where cast(text_indexing_date as date) >= 
    current_date - cast ( ? as interval day)
    

    Also try to eliminiate  the cast in cast(text_indexing_date as date) and just use text_indexing date for much better performnace.

     

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

JavaScript is required for this form.





No, thanks