Parsing ISO8601 Formatted Dates?

[braxtone]

I'm having a heck of a time parsing some really standard date formats. I've got timestamps coming out of an API in full ISO8601 format:

2017-03-30T09:57:45:963-0700
2017-04-13T15:48:46:034-0700
2017-01-16T14:42:05:811-0800
2017-01-16T14:41:04:640-0800
2017-01-16T14:41:04:640-0800

From what I've found, Alteryx doesn't support milliseconds or timezones, so I've had to throw down a Regex parser to tokenize the dates into their elements:

(\d{4})-(\d{2})-(\d{2})T(\d{2})\:(\d{2})\:(\d{2}):(\d{3})([\-\+]\d{4})

and then use a formula to pull in the values Alteryx can understand and format it:

DateTimeParse([EntryDate_Year] + "-" +
	[EntryDate_Month] + "-" +
	[EntryDate_Day] + "T" +
	[EntryDate_Hour] + ":" +
	[EntryDate_Minute] +":" +
	[EntryDate_Second] + 
	[EntryDate_Timezone],"%Y-%m-%dT%H:%M:%S%z")

Is there a better way, or a feature request I should be voting up?

[Philip · Accepted solution]

The attached is a macro we use to handle these cases. Insert and use like you use other tools. Let me know if you have any questions.

[SeanAdams]

Hey @braxtone - there are also a few ideas that you can contribute to too, several of them around decomposing or recomposing dates; and one about rethinking the way that dates are treated in Alteryx (since there are different date treatments for date parsing; date construction; date trimming; date adding; etc).

There's none that specifically addresses ISO date format, but I reckon you should add it.

Here's the full list of ideas in the IDEAS board related directly to date handlin.   Have a look at these, and it would be good to get your thoughts and support on them:

https://community.alteryx.com/t5/Alteryx-Product-Ideas/Refactor-Date-time-treatment/idi-p/46420

https://community.alteryx.com/t5/Alteryx-Product-Ideas/DateTime-Tool-Enhancements-Convert-Multiple-F...

https://community.alteryx.com/t5/Alteryx-Product-Ideas/Time-Based-Function/idi-p/4842

https://community.alteryx.com/t5/Alteryx-Product-Ideas/More-forgiving-evaluations-of-Date-and-DateTi...

https://community.alteryx.com/t5/Alteryx-Product-Ideas/Have-a-way-to-easily-get-date-parts-out-of-a-...

https://community.alteryx.com/t5/Alteryx-Product-Ideas/DateTimeTrim-Function-Add-Week-Trimming/idi-p...

https://community.alteryx.com/t5/Alteryx-Product-Ideas/Add-DateTime-type-with-milliseconds/idi-p/200...

https://community.alteryx.com/t5/Alteryx-Product-Ideas/Alteryx-making-dating-easier/idi-p/20625

https://community.alteryx.com/t5/Alteryx-Product-Ideas/Make-d-and-m-in-DateTimeFormula-work-if-no-le...

https://community.alteryx.com/t5/Alteryx-Product-Ideas/Null-Date-Check/idi-p/13116

https://community.alteryx.com/t5/Alteryx-Product-Ideas/CreateDate-Function/idi-p/55171

https://community.alteryx.com/t5/Alteryx-Product-Ideas/Update-existing-field-in-DateTime-tool/idi-p/...

https://community.alteryx.com/t5/Alteryx-Product-Ideas/Allow-Date-DateTime-Time-output-from-Regex-Pa...

https://community.alteryx.com/t5/Alteryx-Product-Ideas/Update-existing-field-in-DateTime-tool/idi-p/...

https://community.alteryx.com/t5/Alteryx-Product-Ideas/DateTimeFirstOfWeek-Formula-Function/idi-p/14...

https://community.alteryx.com/t5/Alteryx-Product-Ideas/Date-Time-Formatting-Beginning-with-2-Digit-Y...

https://community.alteryx.com/t5/Alteryx-Product-Ideas/Have-a-Time-Question-in-Analytic-Apps/idi-p/1...

https://community.alteryx.com/t5/Alteryx-Product-Ideas/Impute-Dates/idi-p/1509

https://community.alteryx.com/t5/Alteryx-Product-Ideas/DateTimeTrim-for-Year/idi-p/35805

https://community.alteryx.com/t5/Alteryx-Product-Ideas/Next-potential-improvement-to-formula-tools/i...

https://community.alteryx.com/t5/Alteryx-Product-Ideas/Improvements-to-the-Select-Tool/idi-p/33834

https://community.alteryx.com/t5/Alteryx-Product-Ideas/random-inputs/idi-p/54222

https://community.alteryx.com/t5/Alteryx-Product-Ideas/Field-Sumary-tool-should-report-all-data-type...

[braxtone]

Thanks Sean. I wanted to make sure I wasn't missing something obvious before posting a feature request. I think the idea you put out in https://community.alteryx.com/t5/Suggestions-For-Our-Community/Can-we-group-product-ideas-together-a... is a great one and responded to it.

[RyanTM]

Thank, this is super helpful. Note that the original poster's data (and mine) does not have a : between the hh and mm in the timezone offset. I've attached an updated version of the macro which should handle both.