What time is it Alteryx? Part 2

In Part 1 we addressed how to parse a series of dates using the date time Tool and extending that to the formula tool. This time we are going to look at a two of more specific computer language dates. the first is the UNIX time stamp, the second is the MySQL time stamp

DateTime Tool

UNIX Time

UNIX is a common computer operating system and (from my limited experience feel free to correct/expand on this in the comments) is a more common as a server hosting language. As a result UNIX (or POSIX or Epoch) time will often appear as a time stamp for server generated logs.
UNIX time is defined as the number of seconds elapsed since 00:00:00 (Coordinated Universal Time (UTC)) on 1 January 1970. This is similar to the way that Microsoft Excel performs calculations on time just using seconds past instead of days past.

The Parsing Procedure

The process for parsing UNIX time is relatively simple. Using the DateTimeAdd(dt,i,u) formula in the Formula Tool you take the start date, dt, of ‘1970-01-01’, add the UNIX time you have, [your date field], and tell the formula that you are adding seconds.

MySQL Time

MySQL is the second most widely used open-source RDBMS (according to Wikipedia) and the timestamps need a bit of manipulation before use. While the time-stamp is ‘normal’ in what a user sees (e.g. ‘13/01/2015 5:37’), the time portion (5:37) lacks leading zeros creating a challenge when parsing the field. This results in a lots of NULL fields.

Text to Columns opstions to split MySQL timestamps for processing

Using the timestamp example above split the field into the two columns using the ‘Text to Columns’ tool with the delimiter option being a space.
The next step is to add the leading zeros to the time field. This is done with the function tool using the ‘PadLeft(String, len, char)’ function. The string is the time field. Length is set to 5 (‘hh:ss’ = 5 characters) and the Pading character is ‘0’. Then combine the fields back together and apply the DateTimeParse formula as described part 1.

Tamed Time

So using formula tools, the DateTime tool and some mental juggling strings are parsed with Alteryx and ready for use in Time Series prediction, summarisation, or visual analytics in Tableau.
Any other time stamp formats you see frequently or find confusing (Oracle, MS SQL, Excel)? Have any other parsing challenges you face commonly? Pop them in the comments below.

Until Next Time.