This discussion is archived
6 Replies Latest reply: Jun 19, 2013 12:59 PM by pl_sequel RSS

Date truncated in xmlquery?

pl_sequel Newbie
Currently Being Moderated
Running Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

Noticed my xml queries are returning my date values without the time component. Am i correct in assuming this is by design?
create table sample (thedate date, theTimestamp timestamp);
insert into sample values(sysdate, sysdate);

select xmlquery('for $v in fn:collection("oradb:/PUBLIC/SAMPLE")
return ($v/ROW/THEDATE,$v/ROW/THETIMESTAMP)' returning content) from dual;
Result:
<THEDATE>2013-06-07</THEDATE><THETIMESTAMP>2013-06-07T15:31:50.000000</THETIMESTAMP>
So the only way for getting the time would be to change my column datatypes to timestamp?
  • 1. Re: Date truncated in xmlquery?
    markjw Newbie
    Currently Being Moderated

    I don't have the sample code / schema in front of me... but from looking at your results, I think thedate is defined as xs:date and theTimestampe is defined as xs:dateTime.  Note that per xml standards xs:date only stores up to day precision, while xs:dateTime goes does to fractions of a second.  I believe this is why your date value appears 'truncated'.  Here is the w3c link describing the primitive date type: http://www.w3.org/TR/xmlschema-2/#date

  • 2. Re: Date truncated in xmlquery?
    pl_sequel Newbie
    Currently Being Moderated

    Thanks.. so it seems then that Oracle maps the date primitive to xs:date... so the solution is to change the primitive type to one that maps to xs:dateTime..such as timestamp?

  • 3. Re: Date truncated in xmlquery?
    markjw Newbie
    Currently Being Moderated

    Hmmm, actually I went searching through the documentation and it seems pretty vague in regards to this.  This doc has a section on using the URI scheme with fn:collection - http://docs.oracle.com/cd/E18283_01/appdev.112/e16659/xdb_xquery.htm#CBAGCBGJ.  Regarding the result returned by fn:collection it says:

    For a relational table, the root element of each XML document returned by fn:collection is ROW. The children of the ROW element are elements with the same names (uppercase) as columns of the table. The content of a child element corresponds to the column data. That content is an XML element if the column is of type XMLType; otherwise (the column is a scalar type), the content is of type xs:string.

     

    Just from running your sample query, though, it would appear that you'll have to use TIMESTAMP in order to get the date portion.  It seems to me that the string returned will be formatted based on xs:date and xs:dateTime for date and timestamp respectively, but I haven't been able to confirm that in the docs.  I guess you could use a view that converts your data to a timestamp via a cast or a formatted string via to_char?  Not sure if this helps at all for what you need to do.

     

    create or replace view sample_view as (

      select CAST(thedate as timestamp) as thedate, theTimestamp from sample);

     

    select xmlquery('for $v in fn:collection("oradb:/PUBLIC/SAMPLE_VIEW")

    return ($v)' returning content) from dual;


     

     

    XMLQUERY('FOR$VINFN:COLLECTION("ORADB:/PUBLIC/SAMPLE_VIEW")RETURN($V)'RETURN

    --------------------------------------------------------------------------------

    <ROW><THEDATE>2013-06-10T14:27:34.000000</THEDATE><THETIMESTAMP>2013-06-10T14:27

  • 4. Re: Date truncated in xmlquery?
    odie_63 Guru
    Currently Being Moderated

    markjw wrote:

     

    Hmmm, actually I went searching through the documentation and it seems pretty vague in regards to this.  This doc has a section on using the URI scheme with fn:collection - http://docs.oracle.com/cd/E18283_01/appdev.112/e16659/xdb_xquery.htm#CBAGCBGJ.

     

    Most of the time, the fn:collection("oradb:/") construct is resolved internally using SQL/XML functions, so it should follow the associated rules regarding standard formats, in particular :

    http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb13gen.htm#autoId4

  • 5. Re: Date truncated in xmlquery?
    pl_sequel Newbie
    Currently Being Moderated

    Thanks guys for the info.

     

    I can alter my tables and change from date to timestamp without any issues, but i noticed now it doesn't contain the tz info of course. I can't alter the column type from timestamp to timestamp with local timezone, without first flushing the data...

     

    I have queries which compare dates to fn:current-dateTime(), which contains the local tz... I'll have to test whether a comparison against a timestamp without a tz to current-dateTime with a tz works or not.. I assume if no tz info is found, Oracle treats it as local timezone... we shall see.

  • 6. Re: Date truncated in xmlquery?
    pl_sequel Newbie
    Currently Being Moderated
    SELECT XMLQUERY('
    let $date1 := xs:dateTime("2013-06-19T13:12:29.000000+00:00")
    let $date2 := xs:dateTime("2013-06-19T09:12:28.000000-04:00")
    return if ($date1 <= $date2) then (concat($date1," date is less than ", $date2)) else (concat($date1," date is greater than ", $date2)) ' returning content) from dual;
     

    returns:  2013-06-19T13:12:29.000000000+00:00 date is greater than 2013-06-19T09:12:28.000000000-04:00

     

    SELECT XMLQUERY('
    let $date1 := xs:dateTime("2013-06-19T13:12:27.000000+00:00")
    let $date2 := xs:dateTime("2013-06-19T09:12:28.000000-04:00")
    return if ($date1 <= $date2) then (concat($date1," date is less than ", $date2)) else (concat($date1," date is greater than ", $date2)) ' returning content) from dual;

    returns:   2013-06-19T13:12:27.000000000+00:00 date is less than 2013-06-19T09:12:28.000000000-04:00

     

    So it seems that Oracle normalizes the date before comparing... not sure if $date1 is normalized to EST, or if $date2 is normalized to UTC... so in this case, i'd have to alter my column types to timestamp with local timezone it seems... or would the following be a viable option?

     

    SELECT XMLQUERY('
    let $date1 := xs:dateTime("2013-06-19T13:12:27.000000+00:00")
    let $date2 := fn:adjust-dateTime-to-timezone(xs:dateTime("2013-06-19T09:12:28.000000-04:00"))
    return if ($date1 <= $date2) then (concat($date1," date is less than ", $date2)) else (concat($date1," date is greater than ", $date2)) ' returning content) from dual;

    returns:  2013-06-19T13:12:27.000000000+00:00 date is less than  2013-06-19T13:12:28.000000000+00:00

     

    Basically stripping off the tz, which treats it as UTC.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points