unable to fetch result based on date range query.

[dhaval soni]

I want to fetch the record from HSQL database and query is as below:

ao.find(IssuesAD.class, Query.select().where("user=? AND (START_TIME = ? OR END_TIME = ? OR (convert(START_TIME,DATE) < convert(?,DATE) AND convert(END_TIME,DATE) > convert(?,DATE)) )",user,sqlDate,sqlDate)) // also tried by removing 'convert'

when i save or retrieve then i convert it to java.sql.date format.

Above query works to retrieve the dats match to exact date i.e. START_TIME=? or END_TIME=? but it does not work for date range (START_TIME < ? AND END_TIME > ?)

In database records are existed as like - END DATE AS '2013-05-27 00:00:00.000000000',

START DATE AS '2013-05-23 00:00:00.000000000'

and parameter value is '2013-05-24'

Any idea what is wrong here..and i'm not able to get correct result data.

Thanks

[dhaval soni · Accepted solution]

Hi guys

I have found the solution, just need to change the query to use BETWEEN keyword as below:

WHERE USER=? AND ? BETWEEN CAST(START_TIME AS DATE) AND CAST(END_TIME AS DATE)

Thank You

[Bhushan Nagaraj]

Hi Dhaval,

JIRA saves the start time and date time in UNIX timestamp format. Refer to

http://www.unixtimestamp.com/index.php

[dhaval soni]

So, i have to change field type to LONG and store the unixtimestamp into HSQL database ? How could i query to retrieve record based on date range condition (in unixtimestamp field) ?