This article is part of a weekly blog series that delivers tips related to Alteryx Designer by Alteryx.
When working with time-series data, you may need to manipulate time values—for example, when there is too much data, or when you want to align multiple data sources that use different time intervals, such as converting the data into minute-by-minute or hour-by-hour increments. In Alteryx, this can be done with the DateTimeTrim function.
What Is DateTimeTrim function?
For time manipulation tasks like this, Alteryx’s DateTimeTrim function is extremely useful. This function allows you to truncate time below a specified unit. For example, if you have monthly sales data and want to aggregate it on a monthly basis, you can use DateTimeTrim([DateTime], “Month”) to set the date column to the first day of the month.
Now, let’s take a look at the options available in the DateTimeTrim function.
DateTimeTrim(dt,t)- dt : Date data. You can specify a value directly, or you can specify a field of type Date or DateTime.
- t : Specifies the unit to trim to. The units are as follows.
- Year : Sets the value to January 1 at 12:00:00 AM.
- Month : Sets the value to the 1st day of the month at 12:00:00 AM.
- Day : Sets the value to 12:00:00 AM of the corresponding day.
- Hour : Sets the value to 0 minutes and 0 seconds of the corresponding hour.
- Minute : Sets the value to 0 seconds of the corresponding minute.
- seconds : Sets the value to the corresponding second (available when using units smaller than seconds).
- milliseconds : Sets the value to the corresponding millisecond (available when using units smaller than seconds).
- Firstofmonth : Sets the value to the 1st day of the month at 12:00:00 AM. Same as the Month option.
- Lastofmonth : Sets the value to 11:59:59 PM on the last day of the month.
However, this function basically cannot create irregular or arbitrary time intervals. For example, it cannot directly convert data into 5-minute intervals. So this time, I would like to show how to make it work with a specified interval.
Using DateTimeTrim at Any Interval You Like
First, let’s try converting the data into 5-minute intervals.
The basic idea is to extract the minute portion, calculate the remainder when dividing it by 5, and subtract that remainder from the original time. Then, to remove any fractional part, apply DateTimeTrim with the minute unit. If you reproduce this in a single formula, it looks like the following. The name of the input time field is “MyDateTime”.
DateTimeTrim(DateTimeAdd([MyDateTime],-MOD(DateTimeMinutes([MyDateTime]),5),"minutes"),"minutes")If we break it down a little further, it looks like this.
The result looks like the following.
The time calculated by MOD function is output to the “minutes” field. If you subtract this from MyDateTime, all values from 0 to 4 minutes become data at exactly 0 minutes. Incidentally, if your data does not contain seconds, the final DateTimeTrim is unnecessary.
By applying this idea, you can of course easily create 2-hour intervals, 10-second intervals, and so on.
One important point to keep in mind is that, for minute-based intervals, the specified interval should divide evenly into 60 minutes. For example, if you use 25-minute intervals, the times will be 0 minutes, 25 minutes, and 50 minutes, but with the formula above, the sequence becomes 0 minutes, 25 minutes, and 50 minutes again. If you truly want 25-minute intervals, the next value after 50 minutes should be 75 minutes, which means 15 minutes past the next hour (or 1 hour and 15 minutes from the initial time). In that case, you need to think about it as follows.
When You Want the Specified Interval to Remain Constant
In this case, you need to decide on a reference starting point and determine the elapsed time from there. Because you have to define a base time, the workflow may become slightly more complex.
This time, I will always use 12:00:00 AM of the current day as the reference point. In this case, the formula in the Formula tool becomes as follows.
Only the top formula has been changed, but it calculates DateTimeDiff from the reference datetime. Here, the reference time is the value obtained by applying DateTimeTrim to MyDateTime using the day unit. This produces data like the following. As you can see, it is indeed in 25-minute intervals.
In any case, if the interval is too tricky, it becomes hard to understand. So I think it is better to use intervals that divide evenly into one hour or one day. If you use odd time intervals, things can get complicated when looking at the data day by day—for example, the number of data points per day may vary.
If you want to write the formula above as a single Formula expression, it looks like this.
DateTimeTrim(DateTimeAdd([MyDateTime],-MOD(DateTimeDiff([MyDateTime],DateTimeTrim([MyDateTime],"Day"),"minutes"),25),"minutes"),"minutes")If you want to specify the reference time yourself, replace the highlighted part with a specific date, for example, ‘2025-01-01 00:00:00’.
Conclusion
- I introduced a way to do something like DateTimeTrim at any specified time interval.
- I recommend using intervals that divide evenly into one day or one hour whenever possible.
Sample Workflow Download
The next post will be…
Next time, I’ll cover how to import flat files.
コメント