Dates, Timestamps and ISO 8601
May 15, 2007 19 Comments
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.
OraStory 
You can embed literal strings in a format picture quite easily . for example …
To_Char(sysdate,’YYYY-MM-DD”T”HH24:MI:SS”Z”‘)
Something new every day….
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 🙂 but hey… then others wouldn’t come across it.
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.
It was a surprise to me when I found that out as well. Shhhh!
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.
Command
alter session set nls_timestamp_tz_format=’YYYY-MM-DD”T”HH24:MI:SSFFTZR’;
works only as expected if your timezone is like ‘+01:00’.
However, you can also set for example
ALTER SESSION SET TIME_ZONE = ‘Europe/Berlin’;
Then you would a result like
2008-12-15T12:01:55833586Europe/Berlin
Better use
alter session set nls_timestamp_tz_format=’YYYY-MM-DD”T”HH24:MI:SSFF TZH:TZM’;
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
See comments above – ‘yyyy-mm-dd”T”hh24:mi:ss”Z”‘ – just need to get those quotes & double quotes right
Cheers,
Dominic
Hi…I need to convert sysdate to format -> “YYYY-MM-DD hh:mi:ss.s TZD”. Above post take care of most of the part but not “.s” (Decimal Franction of Second)…Can you tell me the way to achieve this ?
Hi Atul,
1. You need a timestamp – i.e. systimestamp not sysdate
2. The format is .FF[1..9]
e.g. select to_char(systimestamp,’YYYY-MM-DD HH24:MI:SS.FF1′) from dual;
Cheers,
Dominic
Thanks for your Response….What I am actually looking for is…
I want to have “Timestamp of format YYYY-MM-DD hh:mi:ss.s TZD”.
Now with following SQL..
Select to_char(sysdate,’YYYY-MM-DD’)||’T’||to_char(sysdate,’hh:mm:ss+HH:MM’) from dual;
I am getting out put as -> 2010-09-28T09:09:28+09:09
Whats missing in this is “.s” – Decimal Fraction of second.
How to achieve the same?
Dates in Oracle don’t have fractions of a second.
I still think you want to work with a timestamp. Your hand-crafted timezone is all wrong – it’s not +9:09.
See the following:
select to_char(systimestamp,’YYYY-MM-DD HH24:MI:SS.FF1 TZR’) from dual;
2010-09-28 09:43:18.6 +01:00
Thanks a Lot….
Following SQL Solved the Problem…
Select to_char(sysdate,’YYYY-MM-DD’)||’T’||to_char(systimestamp,’hh:mm:ss.FF1 TZR’) from dual;
🙂
Still a nonsense.
This is what it should be, surely:
select to_char(systimestamp,’YYYY-MM-DD”T”HH24:MI:SS.FF1 TZR’) from dual;
still a nonsense try it out in oracle 10g and see the error
Thanx for the tip, i was hoping i could specify a literal. 🙂
i have a date like this 2012-07-04T17:00:00-5:00 i need the output like 04-JUL-12, i tried all the format above no luck.
Hi Hary,
You need to think in steps.
Firstly “2012-07-04T17:00:00-5:00” is not a date, it’s a string representation.
So first thing you need is to cast it to a date.
But dates don’t recognise the TZR format so actually you need to cast it to a timestamp, a timestamp with timezone.
Similarly “04-JUL-2012” is a different string representation.
So putting that all together:
SQL> select to_char( 2 to_timestamp_tz('2012-07-04T17:00:00-5:00','YYYY-MM-DD"T"HH24:MI:SSTZR') 3 ,'DD-MON-YYYY') 4 from dual; TO_CHAR(TO_ ----------- 04-JUL-2012Alternatively, do some pattern matching on the string up to the “T” using something like REGEXP_SUBSTR or SUBSTR+INSTR and then use to_char(to_date(…)), e.g.
SQL> select to_char(to_date( 2 regexp_substr('2012-07-04T17:00:00-5:00','[^T]+',1,1) 3 ,'YYYY-MM-DD'),'DD-MON-YYYY') 4 from dual; TO_CHAR(TO_ ----------- 04-JUL-2012Cheers,
Dominic
select to_timestamp_tz(‘2012-08-08T09:06:14.000-07:00′,’YYYY-MM-DD”T”HH24:MI:SS.FF3TZR’)
from dual;
External table DDL,
extract_date char(29) DATE_FORMAT timestamp WITH TIMEZONE mask ‘YYYY-MM-DD”T”HH24:MI:SS.FF3TZR’