Dates, Timestamps and ISO 8601

If there’s one issue that comes back time and again in Oracle, it’s characterset conversions and timezones. If there are two things that come back time and time again in Oracle..

Someone asked me today how to format a date in Oracle into ISO8601 format.
Specifically the format:


First reaction was what is ISO8601?

Turns out that this format is

Complete date plus hours, minutes, seconds and a decimal fraction of asecond
YYYY-MM-DDThh:mm:ss.sTZD (eg 1997-07-16T19:20:30.45+01:00)

From an Oracle perspective, I found a couple of resources from the community.

I wondered about the Z – stands for Z time. Essentially, the same timezone as GMT and UTC.

Anyway, supposedly, to get an ISO 8601 format timestamp we need a timestamp with timezone.

So, it seems that, fair enough, we need to go from date -> timestamp -> timestamp with timezone and then we should be able to get the right format.

Date -> Timestamp is easy using TO_TIMESTAMP.

And we can get from Timestamp -> Timestamp with timezone using FROM_TZ.

What about that format we needed?

Well, it’s then that we seem to fall short.
We can’t get the ‘Z’ but seeing as that is just the equivalent of ‘+00:00′ then the latter is perfectly acceptable as is any other offset. The format mask of TZH:TZM does for this.

But the ‘T’ is problematic. Remember:


Unless, not for the first time, I’m mistaken, this isn’t possible without using some horrible string concatenation.
Which is slightly odd because a search of the 10gR2 documentation shows that the XML Developer’s Kit knows something about this format.

Of course, one simple(ton) “solution” from a date would be:

select to_char(sysdate,'YYYY-MM-DD')||'T'||to_char(sysdate,'HH24:MI:SS')||'Z'
from dual

That ‘Z’ is only valid for those Z-time timezones, but hey, I’m alright Jack, I’m in GMT.

Or rather, of course, if you need a timezone, use a timestamp with timezone.


Get every new post delivered to your Inbox.

Join 76 other followers