ISO-8601 Dates in Java and XQuery

If you've used XQuery for any length of time, you've noticed that it supports a flexible system of types and function to handle dates, times, and durations. You can subtract two xs:dateTime items to produce a duration. You can add a duration to an xs:dateTime item to produce a new xs:dateTime.

You can also cast a string to an xs:dateTime, and format an xs:dateTime to a string - but only if you can work in ISO-8601 format. There's no built-in function to map non-ISO-8601 dates to xs:dateTime, and no built-in way to reformat xs:dateTime items to non-ISO8601 strings.

If you're working with Java, though, you can easily map Java Date objects to the ISO-8601 format:

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

public class ISO8601Utilities
{
    private static DateFormat m_ISO8601Local =
        new SimpleDateFormat ("yyyy-MM-dd'T'HH:mm:ss");

    public static String formatDateTime()
    {
        return formatDateTime (new Date());
    }

    public static String formatDateTime (Date date)
    {
        if (date == null) {
            return formatDateTime (new Date());
        }

        // format in (almost) ISO8601 format
        String dateStr = m_ISO8601Local.format (date);

        // remap the timezone from 0000 to 00:00 (starts at char 22)
        return dateStr.substring (0, 22)
            + ":" + dateStr.substring (22);
    }
}

This class gives you a couple of handy static methods for formatting Java Date objects as ISO-8601 strings. From there, a simple xs:dateTime() cast will get you an XQuery xs:dateTime item.

What if you want to read an xs:dateTime item from the database, and use it as a Java Date object? XCC does most of this work for you:

// we already have an XCC Session sess and a
// String query that returns just one xs:dateTime item.

Date theDate = null;
Request req = sess.newAdhocQuery("current-dateTime()");
ResultSequence rs = sess.submitRequest(req);

theDate = (XSDateTime)(rs.next().getItem()).asDate();

// closing the session will also clean up the result sequence
sess.close();

What if you have already loaded a slew of documents into your database, and you want to transform human-readable dates into ISO-8601 format? You can use XQuery to map any consistently-formatted string to ISO-8601. Here is one example:

xquery version "1.0-ml";

declare function javaDateToDate
    ($javaDate as xs:string?) as xs:dateTime? {

    if (empty($javaDate)) then ()
    else

    let $months := ("Jan", "Feb", "Mar", "Apr", "May", "Jun",
        "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

    (: canonical form: CCYY-MM-DDThh:mm:ss :)

    let $javaRegex :=
        "(\w+)\s+(\d+),\s+(\d+)\s+(\d\d?):([\d:]+)\s+(AM|PM)"
    let $year := replace($javaDate, $javaRegex, "$3")
    let $month := replace($javaDate, $javaRegex, "$1")
    let $day := replace($javaDate, $javaRegex, "$2")
    let $hour := replace($javaDate, $javaRegex, "$4")
    let $mmss := replace($javaDate, $javaRegex, "$5")
    let $ampm := replace($javaDate, $javaRegex, "$6")
    let $hour24 := if ($ampm = "PM")
        then xs:string( (12 + xs:integer($hour)) mod 24)
        else xs:string($hour)
    let $monthNumber := index-of($months, $month)
    let $month00 := if ($monthNumber lt 10)
        then string-join(("0", xs:string($monthNumber)), "")
        else xs:string($monthNumber)
    let $day00 := if (xs:integer($day) lt 10)
        then string-join(("0", $day), "")
        else $day
    let $hour00 := if (xs:integer($hour24) lt 10)
        then string-join(("0", $hour24), "")
        else $hour24

    return xs:dateTime(string-join((
        string-join(($year, $month00, $day00), "-"), "T",
        string-join(($hour00, $mmss), ":")), ""))

}; (: javaDateToDate :)

This function parses the output of the Java Date object's toString() method, and returns an ISO-8601 item. It's best, though, if you can arrange to insert all your date-time information as ISO-8601, in the first place.