6 Replies Latest reply: Jun 19, 2013 2:59 PM by pl_sequel RSS

    Date truncated in xmlquery?

    pl_sequel
      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

          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

            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

              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

                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

                  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
                    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.