Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Parse ISO8601/rfc3339 Date?

I have a number of columns of dates in the RFC3339 format:

2011-11-18T10:57:02-07:00

In strftime format, this is %Y-%m-%dT%H:%M:%S%z, so the "-07:00" is the time-zone.

How do I parse this in the date#() function? The documentation doesn't mention time zones when loading data, just altering the time zone of an already loaded date.

Tags (2)
1 Solution

Accepted Solutions
johnw
Not applicable

Re: Parse ISO8601/rfc3339 Date?

Well, I don't think QlikView can do it easily.  But you can break the problem into pieces by pulling the string apart and parsing each bit separately.  Here's one approach that seems to be working.

timestamp(date#(mid('2011-11-18T10:57:02-07:00',1,10),'YYYY-MM-DD')

         +time#(mid('2011-11-18T10:57:02-07:00',12,8),'hh:mm:ss')

             $(=mid('2011-11-18T10:57:02-07:00',20,1))

      interval#(mid('2011-11-18T10:57:02-07:00',21,5),'hh:mm'))

Edit: Or did I get the sign wrong?  Let's see, -7:00 would be 7 hours earlier than UTC, right?  So we actually want to add to get to UTC, right?

timestamp(date#(mid('2011-11-18T10:57:02-07:00',1,10),'YYYY-MM-DD')

         +time#(mid('2011-11-18T10:57:02-07:00',12,8),'hh:mm:ss')

            +if(mid('2011-11-18T10:57:02-07:00',20,1)='+',-1,+1)

     *interval#(mid('2011-11-18T10:57:02-07:00',21,5),'hh:mm'))

2 Replies
johnw
Not applicable

Re: Parse ISO8601/rfc3339 Date?

Well, I don't think QlikView can do it easily.  But you can break the problem into pieces by pulling the string apart and parsing each bit separately.  Here's one approach that seems to be working.

timestamp(date#(mid('2011-11-18T10:57:02-07:00',1,10),'YYYY-MM-DD')

         +time#(mid('2011-11-18T10:57:02-07:00',12,8),'hh:mm:ss')

             $(=mid('2011-11-18T10:57:02-07:00',20,1))

      interval#(mid('2011-11-18T10:57:02-07:00',21,5),'hh:mm'))

Edit: Or did I get the sign wrong?  Let's see, -7:00 would be 7 hours earlier than UTC, right?  So we actually want to add to get to UTC, right?

timestamp(date#(mid('2011-11-18T10:57:02-07:00',1,10),'YYYY-MM-DD')

         +time#(mid('2011-11-18T10:57:02-07:00',12,8),'hh:mm:ss')

            +if(mid('2011-11-18T10:57:02-07:00',20,1)='+',-1,+1)

     *interval#(mid('2011-11-18T10:57:02-07:00',21,5),'hh:mm'))

Not applicable

Re: Parse ISO8601/rfc3339 Date?

Thanks, that worked.

Since I have about 10 columns that need the same parsing, I made this into a dollar-expression:

SET parse8601=timestamp(date#(mid($1,1,10),'YYYY-MM-DD')

         +time#(mid($1,12,8),'hh:mm:ss')

            +if(mid($1,20,1)='+',-1,+1)

     *interval#(mid($1,21,5),'hh:mm'));