community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Knowledge Base

Definitive answers from Designer experts.
New Data Science Blog

Check out the latest post: All Models Are Wrong

READ MORE

When Time is of the Essence: DateTime Functions with the Formula Tool

Sr. Instructional Designer
Sr. Instructional Designer

If you're using the Formula Tool in v11.0+, be sure to familiarize yourself with the tool's interface redesign!

 

In 1925, the small town of Nome in Alaska suffered a diphtheria outbreak.  Out of usable antitoxin and in the depths of winter, the town doctor telegrammed the governor in Juneau and authorities in Washington D.C. requesting a million units of antitoxin to stave off an epidemic.  Confronted with the task of saving the people of Nome, Alaskan authorities debated their options of getting the antitoxin to Nome.  With sub-zero temperatures, limited daylight and a lack of experienced pilots, airlifting the supplies was a less than ideal solution.  Instead, a dogsled relay was selected to transport the antitoxin across the 674 miles from Nenana to Nome.  The mushers endured near-blizzard conditions and hurricane-force winds to deliver the serum five and a half days later, saving the small town from a deadly outbreak. 

 

While you may not have the fate of a small town on your hands, understanding the time elements in your data is critical for your analyses.  Perhaps you need to determine a product’s time in transit between two ports or warehouses, identify non-business days in your dataset to correctly account for time, or re-format data to work on a 12 or 24 hour clock.  In all of these cases, time is of the essence! 

 

To demonstrate the use of the Formula Tool’s DateTime functions, and in the spirit of the Alaskan dogsledding tradition, we’ll work with data from the last leg of the Iditarod, which stretches from Safety to Nome.  Using the data provided from the 2016 race, we will 1) calculate the elapsed time for each musher between the two checkpoints, 2) determine the Iditarod’s start date and time from a musher’s total race time and arrival time in Nome, and 3) re-format the time format from a 24 to 12 hour clock.  Before beginning with Formula Tool’s DateTime functions, make sure that your data should be appropriately formatted as a DateTime field type using the DateTime tool or a Select Tool.*

 

 *The only acceptable string format for use with DateTime functions is yyyy-mm-dd HH:MMSmiley FrustratedS, which is Alteryx’s native string format.

DateTime_tool.jpg

The Input data contains the time each musher left Safety (Departure) and the time that they arrived in Nome (Arrival), finishing the race.  To calculate the time it took for each musher to complete this final leg of the race, we’ll find the difference between the start (Departure) and end (Arrival) times using the DateTimeDiff function:

 

DateTimeDiff (<date/time1>, <date/time2>, <units>)

                                                                                      [Arrival]           [Departure]     ‘seconds’ DateTime_Diff.jpg

The expression returns the elapsed time between the Arrival and Departure times as years, months, days, hours, minutes or seconds as a numeric field type.  In this case, the expression returns the number of seconds (Int64) between the Departure and Arrival times, which can be used downstream to calculate hours (Seconds/3600) and minutes (Seconds/60).

 

The sample dataset not only provides data for the final leg of the 2016 Iditarod but also each musher’s time for the entirety of the race (Total Race Time).  Using these pieces of information, we can determine the time at which the mushers started the race in Anchorage by subtracting the musher’s total race time from their completion time (Arrival) in Nome.  After a bit of parsing and simple math, we can convert a Total Race Time of 8d 11h 20m 16s to its value of 732016 seconds (Total Seconds).  Then, we can subtract the race time (in seconds) from each musher’s arrival time in Nome to determine the start time of the race using the DateTimeAdd function:

 

DateTimeAdd (<date/time>,        <interval>,     <units>)

                                                            [Arrival],       -[Total Seconds]*,    ‘seconds’

 

*Because we are looking to determine a DateTime in the past, we use a negative value for the time interval.  When looking for a DateTime in the future, ensure that your time interval is a positive numeric value.

DateTimeAdd.jpg 

 

After applying our DateTimeAdd function, we find that the 2016 Iditarod began at 2016-03-06 15:00:00.  That’s not the easiest format to understand, so let’s take advantage of the power of the DateTime Format function.  The DateTime Format tool allows you to indicate a format specifier to parse components of a DateTime field.  A format specifier begins with a percent sign (%), followed by a case-sensitive letter.  For a complete list of supported format specifiers, please refer to the Formula Tool’s help documentation.      

 

For our particular example, let’s identify the day of the week, the month, date and time (on a 12 hour clock) that the race started.  We use the format identifiers to create a string field that contains this information.  It is important to note that each format specifier is treated as text and needs to be wrapped in single quotes (see example below). 

 

Using a series of format specifiers, we can see that the 2016 Iditarod began on Sunday, March 6, 2016 at 3:00 PM.  When time, whether when dogsledding or managing your business operations, is of the essence, you can count on the DateTime functions to save the day! 

DateTimeFormat.jpg