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:
2007-05-15T16:45:00Z
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:
2007-05-15T16:45:00Z
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.
6 responses so far ↓
David Aldridge // May 15, 2007 at 4:31 pm |
You can embed literal strings in a format picture quite easily . for example …
To_Char(sysdate,’YYYY-MM-DD”T”HH24:MI:SS”Z”‘)
dombrooks // May 15, 2007 at 4:36 pm |
Something new every day….
but hey… then others wouldn’t come across it.
You’d think I’d have come across that before now.
I feel like a total wally now.
I should really delete the post to save face
In my very thin defence….um, er …. still seems a little odd that XML documentation alludes to it as an official format but not to_char…. that’s the best I can come up with.
Thanks David.
David Aldridge // May 15, 2007 at 9:21 pm |
It was a surprise to me when I found that out as well. Shhhh!
Adderek // December 15, 2008 at 11:06 am |
You can use the following:
OPTION A:
alter session set nls_timestamp_tz_format=’YYYY-MM-DD”T”HH24:MI:SSFFTZR’;
select current_timestamp from dual;
CURRENT_TIMESTAMP
—————————————————————————
2008-12-15T12:01:55833586+01:00
OPTION B:
select to_char(current_timestamp,’YYYY-MM-DD”T”HH24:MI:SS.FFTZR’) from dual;
TO_CHAR(CURRENT_TIMESTAMP,’YYY
—————————————————————————
2008-12-15T12:04:02.652233+01:00
I prefer the option A – to always set a custom time/date formats. This way I can locate any code where someone (hopefully not me) hardcoded the time/date format.
jianping // June 10, 2009 at 9:06 pm |
I have a slightly different problem: I need to convert 2007-05-15T16:45:00Z back to a date on oracle. What is the data format? I tried
‘yyyy-mm-dd”Thh24:mi:ss”Z’. It doesn’t work
dombrooks // June 11, 2009 at 9:34 am |
See comments above – ‘yyyy-mm-dd”T”hh24:mi:ss”Z”‘ – just need to get those quotes & double quotes right
Cheers,
Dominic