Datypic Logo
XQuery

XQuery

Priscilla Walmsley (pwalmsley@datypic.com)

ISBN: 0596006349

1st edition, 2007, O'Reilly Media, Inc.

Chapter 19: Working with dates, times and durations

Useful function: MMDDYYYY-to-date (see also functx:mmddyyyy-to-date)
declare namespace functx = "http://www.functx.com";
declare function functx:MMDDYYYY-to-date
($dateString as xs:string?) as xs:date
  {
    xs:date(
      let $dateWithoutSeps := replace($dateString,"[^\d]", "")
      return concat( substring($dateWithoutSeps, 5,4), "-",
                     substring($dateWithoutSeps, 1,2), "-",
                     substring($dateWithoutSeps, 3,2))
    )
  };
Table 19-2. Examples of comparing dates and times
ExampleValue
xs:time("17:15:20–00:00") = xs:time("12:15:20–05:00")true
xs:time("12:15:20–05:00") > xs:time("12:15:20–04:00")true
xs:time("12:15:20–05:00") = xs:time("12:15:20")true
xs:dateTime("2006-05-03T05:00:00") = xs:dateTime("2006-05-03T07:00:00")false
xs:date("2006-12-25") < xs:date("2005-01-06")false
xs:time("12:15:20") > xs:time("12:15:30")false
xs:date("2006-05-06") > xs:dateTime("2006-05-03T13:20:00")Type error
The third example assumes that the implicit time zone (implementation-defined) is -05:00.
Table 19-4. Examples of comparing durations
ExampleValue
xs:yearMonthDuration("P1Y") = xs:yearMonthDuration("P1Y")true
xs:yearMonthDuration("P1Y") = xs:yearMonthDuration("P12M")true
xs:yearMonthDuration("P1Y2M") > xs:yearMonthDuration("P1Y3M")false
xs:dayTimeDuration("P1DT12H") = xs:dayTimeDuration("PT36H")true
xs:dayTimeDuration("P1DT12H") < xs:dayTimeDuration("PT37H")true
xs:yearMonthDuration("P1Y") < xs:dayTimeDuration("P366D")Type error
xs:duration("P11M") < xs:duration("P12M")Type error
Table 19-6. Examples of the component extraction functions
ExampleReturn value
month-from-date(xs:date("1999-05-31"))5
hours-from-time(xs:time("09:54:00"))9
seconds-from-duration(xs:duration("P5DT1H13.6S"))13.6
seconds-from-duration(xs:duration("-P5DT1H13.6S"))-13.6
years-from-duration(xs:duration("P5Y3M"))5
years-from-duration(xs:duration("P1Y15M"))2
Table 19-7. Examples of subtracting dates and times
ExampleValue
xs:dateTime("2006-04-11T09:23:30.5") - xs:dateTime("2006-04-04T02:15:10.2")P7DT7H8M20.3S
xs:dateTime("2006-05-03T12:15:30.5") - xs:dateTime("2006-05-03T12:15:10.2")PT20.3S
xs:date("2006-05-06") - xs:date("2006-05-03")P3D
xs:date("2006-04-02") - xs:date("2005-03-11")P387D
xs:date("2006-05-03") - xs:date("2006-05-03")PT0S
xs:date("2006-05-03") - xs:date("2006-05-06")-P3D
xs:time("13:12:02.001") - xs:time("13:12:00")PT2.001S
xs:time("13:12:00–03:00") - xs:time("13:12:00–05:00")-PT2H
xs:time("08:00:00–05:00") - xs:time("09:00:00–02:00")PT2H
xs:time("13:12:00–03:00") - xs:time("13:12:00")-PT2H
Table 19-8. Examples of adding durations to dates and times
ExampleValue
xs:dateTime("2006-05-03T09:12:35") + xs:yearMonthDuration("P1Y2M")2007-07-03T09:12:35
xs:dateTime("2006-04-29T09:12:35") + xs:dayTimeDuration("P5DT2H12M")2006-05-04T11:24:35
xs:dateTime("2006-04-29T09:12:35") + xs:dayTimeDuration("P5DT17H12M")2006-05-05T02:24:35
xs:dateTime("2006-04-29T09:12:35") - xs:yearMonthDuration("P1Y")2005-04-29T09:12:35
xs:yearMonthDuration("P1Y5M") + xs:date("2006-10-02")2008-03-02
xs:date("2006-10-02") - xs:dayTimeDuration("PT48H")2006-09-30
xs:date("2006-03-31") - xs:yearMonthDuration("P1M")2006-02-28
xs:time("09:12:35") + xs:dayTimeDuration("P5DT2H12M")11:24:35
xs:time("09:12:35") + xs:yearMonthDuration("P1Y2M")Type error
Table 19-9. Examples of adding and subtracting durations
ExampleValue
xs:yearMonthDuration("P3Y10M") + xs:yearMonthDuration("P5Y5M")P9Y3M
xs:dayTimeDuration("P2DT14H55.3S") - xs:dayTimeDuration("P1DT12H51.2S")P1DT2H4.1S
xs:dayTimeDuration("P1DT12H51.2S") - xs:dayTimeDuration("P2DT14H55.3S")-P1DT2H4.1S
xs:yearMonthDuration("P3Y10M") + xs:dayTimeDuration("P1DT12H")Type error
Table 19-10. Examples of multiplying and dividing durations
ExampleValueValue type
xs:yearMonthDuration("P1Y6M") * 3.5P5Y3Mxs:yearMonthDuration
3 * xs:dayTimeDuration("PT50M")PT2H30Mxs:dayTimeDuration
xs:yearMonthDuration("P2Y6M") div 2P1Y3Mxs:yearMonthDuration
10 * xs:yearMonthDuration("P1M")P10Mxs:yearMonthDuration
Table 19-11. Examples of multiplying and dividing durations
ExampleValue
xs:yearMonthDuration("P1Y") div xs:yearMonthDuration("P6M")2
xs:dayTimeDuration("PT25M") div xs:dayTimeDuration("PT50M")0.5
xs:dayTimeDuration("PT2H") div xs:dayTimeDuration("PT1M")120
Datypic XQuery Services