You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by MichaelSK <n-...@post.sk> on 2009/05/19 12:15:04 UTC
date doesn't include hours, minutes, seconds
Hello,
I’ve a problem with select that is created by IBatis. In my .xml file I’ve
the following statement:
SELECT b.id, b.id_credit, b.status, b.tag, b.ins_time, b.modif_time
FROM ws.vi_batchcompl b
<dynamic prepend="WHERE">
<isEqual prepend=" AND "
property="searchFilter.completionStatusType" compareValue="1">
decode(status, 'a', 1, 'c', 2, 'n', 3, 'x', 4, NULL) IN (1, 4)
</isEqual>
<isEqual prepend=" AND "
property="searchFilter.completionStatusType" compareValue="2">
decode(status, 'a', 1, 'c', 2, 'n', 3, 'x', 4, NULL) = 2
</isEqual>
<isEqual prepend=" AND "
property="searchFilter.completionStatusType" compareValue="3">
decode(status, 'a', 1, 'c', 2, 'n', 3, 'x', 4, NULL) = 3
</isEqual>
<isEqual prepend=" AND "
property="searchFilter.completionStatusType" compareValue="4">
decode(status, 'a', 1, 'c', 2, 'n', 3, 'x', 4, NULL) = 4
</isEqual>
<isNotNull prepend=" AND " property="completionInsTimeFrom">
<![CDATA[b.ins_time >= #completionInsTimeFrom#]]>
</isNotNull>
<isNotNull prepend=" AND " property="completionInsTimeTo">
<![CDATA[b.ins_time <= #completionInsTimeTo#]]>
</isNotNull>
</dynamic>
Variables completionInsTimeFrom and completionInsTimeTo have type
java.util.Date. IBatis creates this select:
SELECT b.id, b.id_credit, b.status, b.tag, b.ins_time, b.modif_time
FROM ws.vi_batchcompl b
WHERE decode(status, 'a', 1, 'c', 2, 'n', 3, 'x', 4, NULL) IN (1, 4)
AND b.ins_time >= to_date('2009-05-11', 'yyyy-mm-dd')
AND b.ins_time <= to_date('2009-05-15', 'yyyy-mm-dd')
The problem is that I want to compare the dates using also hours, minutes
and seconds. I want this select to look like this:
SELECT b.id, b.id_credit, b.status, b.tag, b.ins_time, b.modif_time
FROM ws.vi_batchcompl b
WHERE decode(status, 'a', 1, 'c', 2, 'n', 3, 'x', 4, NULL) IN (1, 4)
AND b.ins_time >= to_date('2009-05-11 00.00.00', 'yyyy-mm-dd hh24.mi.ss')
AND b.ins_time <= to_date('2009-05-15 23.59.59', 'yyyy-mm-dd hh24.mi.ss')
I cannot use java.util.Timestamp because there is an index on the column and
when I use Timestamp Oracle won’t use the index and but uses full scan. Can
you please help me how to solve this problem?
Michael
--
View this message in context: http://www.nabble.com/date-doesn%27t-include-hours%2C-minutes%2C-seconds-tp23613367p23613367.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
Re: date doesn't include hours, minutes, seconds
Posted by Nicholoz Koka Kiknadze <ki...@gmail.com>.
> IBatis creates this select:
> ....
> to_date('2009-05-15 23.59.59', 'yyyy-mm-dd hh24.mi.ss')
Well, there's no to_date function in your XML, so iBatis can not create it.
It only prepares statement (ps) with
AND b.ins_time >= ?
AND b.ins_time <= ?
and calls ps.setDate
Could you re-check your xml files and re-check iBatis logs, I'm sure iBatis
is not 'smart' enough to add Oracle-specific to_date functions to generated
sql.
On Tue, May 19, 2009 at 10:15 AM, MichaelSK <n-...@post.sk> wrote:
>
> Hello,
>
> I’ve a problem with select that is created by IBatis. In my .xml file I’ve
> the following statement:
>
> SELECT b.id, b.id_credit, b.status, b.tag, b.ins_time, b.modif_time
> FROM ws.vi_batchcompl b
>
> <dynamic prepend="WHERE">
>
> <isEqual prepend=" AND "
> property="searchFilter.completionStatusType" compareValue="1">
> decode(status, 'a', 1, 'c', 2, 'n', 3, 'x', 4, NULL) IN (1, 4)
> </isEqual>
> <isEqual prepend=" AND "
> property="searchFilter.completionStatusType" compareValue="2">
> decode(status, 'a', 1, 'c', 2, 'n', 3, 'x', 4, NULL) = 2
> </isEqual>
> <isEqual prepend=" AND "
> property="searchFilter.completionStatusType" compareValue="3">
> decode(status, 'a', 1, 'c', 2, 'n', 3, 'x', 4, NULL) = 3
> </isEqual>
> <isEqual prepend=" AND "
> property="searchFilter.completionStatusType" compareValue="4">
> decode(status, 'a', 1, 'c', 2, 'n', 3, 'x', 4, NULL) = 4
> </isEqual>
>
> <isNotNull prepend=" AND " property="completionInsTimeFrom">
> <![CDATA[b.ins_time >= #completionInsTimeFrom#]]>
> </isNotNull>
> <isNotNull prepend=" AND " property="completionInsTimeTo">
> <![CDATA[b.ins_time <= #completionInsTimeTo#]]>
> </isNotNull>
> </dynamic>
>
> Variables completionInsTimeFrom and completionInsTimeTo have type
> java.util.Date. IBatis creates this select:
>
> SELECT b.id, b.id_credit, b.status, b.tag, b.ins_time, b.modif_time
> FROM ws.vi_batchcompl b
> WHERE decode(status, 'a', 1, 'c', 2, 'n', 3, 'x', 4, NULL) IN (1, 4)
> AND b.ins_time >= to_date('2009-05-11', 'yyyy-mm-dd')
> AND b.ins_time <= to_date('2009-05-15', 'yyyy-mm-dd')
>
> The problem is that I want to compare the dates using also hours, minutes
> and seconds. I want this select to look like this:
>
> SELECT b.id, b.id_credit, b.status, b.tag, b.ins_time, b.modif_time
> FROM ws.vi_batchcompl b
> WHERE decode(status, 'a', 1, 'c', 2, 'n', 3, 'x', 4, NULL) IN (1, 4)
> AND b.ins_time >= to_date('2009-05-11 00.00.00', 'yyyy-mm-dd hh24.mi.ss')
> AND b.ins_time <= to_date('2009-05-15 23.59.59', 'yyyy-mm-dd hh24.mi.ss')
>
> I cannot use java.util.Timestamp because there is an index on the column
> and
> when I use Timestamp Oracle won’t use the index and but uses full scan. Can
> you please help me how to solve this problem?
>
> Michael
>
> --
> View this message in context:
> http://www.nabble.com/date-doesn%27t-include-hours%2C-minutes%2C-seconds-tp23613367p23613367.html
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>