What time is it Alteryx? Part 2

This post was originally posted on The Information Lab Blog on February 6, 2015

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.jpg
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.

1 thought on “What time is it Alteryx? Part 2”

  1. I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in AlterYX , kindly contact us http://www.maxmunus.com/contact
    MaxMunus Offer World Class Virtual Instructor led training On AlterYX . We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
    For Demo Contact us.
    Saurabh Srivastava
    MaxMunus
    E-mail: [email protected]
    Skype id: saurabhmaxmunus
    Ph:+91 8553576305 / 080 – 41103383
    http://www.maxmunus.com/

Comments are closed.