About the XML(DB) W3C DateTime format while converting Java DateTime

Triggered by a post from Eddie Awad about Current Date in an Oracle database called “Give Me The Current Date Please”, I thought about a XMLDB Forum discussion that ended up in a discussion about JavaDateTime. This discussion focussed me also on XML date time formats. Those XML (W3C) date time formats are explained on their website under the section “XML Schema Part 2 – Datatypes Second Edition“.

Two questions came to my mind:

1. What is a java date time?
2. What is the official XML format date time mask that should be used?

After some searching and asking my developer AMIS colleagues some questions, it came down to the following answer regarding a definition for java date time:

• JavaDataTime: is the difference in milliseconds from 1970.
• The official XML date time format mask is (as described here):

These were for me important questions, because Oracle XMLDB follows the rules of the W3C consortium regarding XML, XML Schemata, etc.

The official W3C datetime format is as follows:

'-'? yyyy '-' mm '-' dd 'T' hh ':' mm ':' ss ('.' s+)? (zzzzzz)?

This is lexical space is explained in the W3C documentation (also in the section “XML Schema Part 2 – Datatypes Second Edition“) as:

* ‘-‘? yyyy is a four-or-more digit optionally negative-signed numeral that represents the year; if more than four digits, leading zeros are prohibited, and ‘0000’ is prohibited; also note that a plus sign is not permitted);

* the remaining ‘–‘s are separators between parts of the date portion;

* the first mm is a two-digit numeral that represents the month;

* dd is a two-digit numeral that represents the day;

* ‘T‘ is a separator indicating that time-of-day follows;

* hh is a two-digit numeral that represents the hour; ’24’ is permitted if the minutes and seconds represented are zero, and the dateTime value so represented is the first instant of the following day (the hour property of a dateTime object in the ·value space· cannot have a value greater than 23);

* ‘:‘ is a separator between parts of the time-of-day portion;

* the second mm is a two-digit numeral that represents the minute;

* ss is a two-integer-digit numeral that represents the whole seconds;

* ‘.’ s+ (if present) represents the fractional seconds;

* zzzzzz (if present) represents the timezone.

So for example 0001-01-01T01:01:01 and / or it can fractional seconds and / or timezone information can be included.

In Oracle database terms this could be the following date format masks (in this case without timezone information):

to_char(to_date(sysdate,'YYYY-MM-DD"T"HH24:MI:SS))

to_timestamp(somestuff, 'YYYY-MM-DD"T"HH24:MI:SS.FF6)
.

The discussion in the XMLDB forum came down for me answering the question: “how can I convert the javaDateTime to XML W3C date format”. The following code gave me a possible answer (there are probably a zillion ways to format and/or do this):

Given that the javaDateTime=1195851922000, then you can get the Oracle (XMLDB XML) date time format via:

SQL> select TO_CHAR((TO_TIMESTAMP ('1970-01-01 00:00:00.000000', 
                                   'YYYY-MM-DD HH24:MI:SS.ff6') 
                                   + ((1195851922000/1000)/86400)
                    ),'YYYY-MM-DD"T"HH24:MI:SS') 
            as "XML_W3C_DATETIME"
     from dual;
 
XML_W3C_DATETIME
-------------------
2007-11-23T21:05:22
 
1 row selected
.

HTH

M.