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:


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.

About these ads

18 Responses to Dates, Timestamps and ISO 8601

  1. You can embed literal strings in a format picture quite easily . for example …

    To_Char(sysdate,’YYYY-MM-DD”T”HH24:MI:SS”Z”‘)

  2. dombrooks says:

    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.

  3. It was a surprise to me when I found that out as well. Shhhh!

  4. Adderek says:

    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.

  5. jianping says:

    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

  6. dombrooks says:

    See comments above – ‘yyyy-mm-dd”T”hh24:mi:ss”Z”‘ – just need to get those quotes & double quotes right

    Cheers,
    Dominic

  7. Atul Yadav says:

    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 ?

    • dombrooks says:

      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

      • Atul Yadav says:

        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?

      • dombrooks says:

        Dates in Oracle don’t have fractions of a second.

      • dombrooks says:

        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

  8. Atul Yadav says:

    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;
    :)

  9. Brian Tkatch says:

    Thanx for the tip, i was hoping i could specify a literal. :)

  10. Hary says:

    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.

    • Dom Brooks says:

      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-2012
      

      Alternatively, 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-2012
      

      Cheers,
      Dominic

  11. 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’

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 75 other followers

%d bloggers like this: