15 February 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[/caption]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.
Text to Columns options to split MySQL timestamps for processing[/caption]Using the time-stamp 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.
UNIX Time
[caption id='attachment_5259' align='alignleft' width='125'] DateTime Tool[/caption]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.[caption id='attachment_5254' align='aligncenter' width='508'] Text to Columns options to split MySQL timestamps for processing[/caption]Using the time-stamp 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.