Сравнение дат в Oracle XQuery (XMLQuery/XMLTable)
При попытке реализовать некоторые проверки в базе данных Oracle с использованием XQuery Мне нужно сравнить, если две даты совпадают, но не могу сделать это очевидным образом, потому что приведение к xs:date на самом деле не удаляйте часть времени из значения xs:dateTime,
Сам по себе запрос, кажется, хорошо работает в другой среде (например, http://www.xpathtester.com/xquery).
Я пропустил что-то важное, или этот случай - просто ошибка и требует специального обходного пути (преобразование в строковые значения для сравнения, сравнения лет, месяцев и дат обеих дат по отдельности и т. Д.)?
Небольшой пример...
Предположим, у нас есть простой XML:
<root>
<date_value>2015-09-11T15:25:55</date_value>
</root>
и хочу сравнить date_value с фиксированным значением xs:date('2015-09-11') игнорируя часть времени.
Сначала преобразуйте содержимое узла в нужный тип и удалите временную часть, приведя его к xs:date:
xs:date(xs:dateTime($doc/root/date_value))
Если мы выберем это значение с XMLQuery() при передаче документа выше как $docмы получили ожидаемый результат:
2015-09-11+00:00
Хорошо. Кажется, что часть времени удалена, но сравнение не удалось:
xs:date(xs:dateTime($doc/root/date_value)) eq xs:date('2015-09-11')
возвращается falseи если мы попытаемся посмотреть на разницу между значениями в выражении, а не сравнивать их:
xs:date(xs:dateTime($doc/root/date_value)) - xs:date('2015-09-11')
мы видим 'PT15H25M55S', которые точно соответствуют временной части date_value,
Запрос со всеми выражениями выше для тестирования:
select
XMLCast(
XMLQuery( column_value
passing
xmltype(q'[
<root>
<date_value>2015-09-11T15:25:55</date_value>
</root>
]') as "doc"
returning content
)
as varchar2(4000)
) result_value,
column_value expression
from
table(sys.odcivarchar2list(
q'[ xs:date(xs:dateTime($doc/root/date_value)) ]',
q'[ xs:date('2015-09-11') ]',
q'[ xs:date(xs:dateTime($doc/root/date_value)) eq xs:date('2015-09-11') ]',
q'[ xs:date(xs:dateTime($doc/root/date_value)) - xs:date('2015-09-11') ]'
))
Поведение воспроизводится на этой версии Oracle:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
а также
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
Обновить
Спасибо collapsar и Alex Poole за ответы, которые дают мне основную идею о правильном обходном пути. Но пытаясь объяснить суть проблемы, я упростил наш вариант использования, который включает некоторую арифметику дат и реальный обходной путь, который будет выглядеть как запрос ниже.
select
XMLCast(
XMLQuery(
q'[
let
$date1 := fn:dateTime(
adjust-date-to-timezone(
xs:date(xs:dateTime($doc/root/date_value)),
()
),
adjust-time-to-timezone( xs:time('00:00'), ())
),
$date2 := fn:dateTime(
adjust-date-to-timezone(
xs:date(xs:dateTime($doc/root/date_value2)),
()
),
adjust-time-to-timezone( xs:time('00:00'), ())
)
return
$date1 + xs:yearMonthDuration('P1Y') - xs:dayTimeDuration('P1D')
eq
$date2
]'
passing
xmltype(q'[
<root>
<date_value>2015-09-11T01:02:03-11:00</date_value>
<date_value2>2016-09-10T10:20:30+13:00</date_value2>
</root>
]') as "doc"
returning content
)
as varchar2(4000)
) result_value
from
dual
2 ответа
Подъем буквального значения даты до значения dateTime делает свое дело (извлекая правильное смещение времени дня из предоставленного значения dateTime):
xs:dateTime($doc/root/date_value) eq fn:dateTime(xs:date('2015-09-11'), xs:time(xs:dateTime($doc/root/date_value)))
Это решение также будет работать для входных данных, лексизированных только как даты.
Вы можете видеть из преобразования обратно в dateTime что время было сохранено; что не помогает, но я не уверен, является ли это ошибкой или ожидаемым поведением - я представляю последнее в мире Oracle и не вижу никаких ссылок на это поведение в MOS...
Вы могли бы сравнить с dateTime диапазон вместо:
select
XMLCast(
XMLQuery( column_value
passing
xmltype(q'[
<root>
<date_value>2015-09-11T15:25:55</date_value>
</root>
]') as "doc"
returning content
)
as varchar2(4000)
) result_value,
column_value expression
from
table(sys.odcivarchar2list(
q'[ xs:date(xs:dateTime($doc/root/date_value)) ]',
q'[ xs:date('2015-09-11') ]',
q'[ xs:date(xs:dateTime($doc/root/date_value)) eq xs:date('2015-09-11') ]',
q'[ xs:date(xs:dateTime($doc/root/date_value)) - xs:date('2015-09-11') ]',
q'[ xs:dateTime($doc/root/date_value) ]',
q'[ xs:dateTime(xs:date(xs:dateTime($doc/root/date_value))) ]',
q'[ xs:dateTime($doc/root/date_value) ge xs:dateTime('2015-09-11T00:00:00') ]',
q'[ xs:dateTime($doc/root/date_value) lt xs:dateTime('2015-09-12T00:00:00') ]',
q'[ xs:dateTime($doc/root/date_value) ge xs:dateTime('2015-09-11T00:00:00')
and xs:dateTime($doc/root/date_value) lt xs:dateTime('2015-09-12T00:00:00') ]'
))
/
Который дает:
RESULT_VALUE EXPRESSION
---------------------------------------- -------------------------------------------------------------------------------------
2015-09-11+00:00 xs:date(xs:dateTime($doc/root/date_value))
2015-09-11+00:00 xs:date('2015-09-11')
false xs:date(xs:dateTime($doc/root/date_value)) eq xs:date('2015-09-11')
PT15H25M55S xs:date(xs:dateTime($doc/root/date_value)) - xs:date('2015-09-11')
2015-09-11T15:25:55.000000+00:00 xs:dateTime($doc/root/date_value)
2015-09-11T15:25:55.000000+00:00 xs:dateTime(xs:date(xs:dateTime($doc/root/date_value)))
true xs:dateTime($doc/root/date_value) ge xs:dateTime('2015-09-11T00:00:00')
true xs:dateTime($doc/root/date_value) lt xs:dateTime('2015-09-12T00:00:00')
true xs:dateTime($doc/root/date_value) ge xs:dateTime('2015-09-11T00:00:00')
and xs:dateTime($doc/root/date_value) lt xs:dateTime('2015-09-12T00:00:00')
Вам нужно создать две даты для сравнения вместо одной, что может или не может быть проблемой для вас, в зависимости от того, откуда они приходят и как вы строите свой реальный запрос. Возможно, более сложный, но, возможно, немного более явный, чем подстрока @collapsar.