Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Parsing ISO8601 Formatted Dates?

braxtone
6 - Meteoroid

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?

4 REPLIES 4
Philip
12 - Quasar

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
17 - Castor
17 - Castor

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
6 - Meteoroid

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
5 - Atom

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.

Labels