This week I came across a neat little trick to round DATETIMEs in tableau to the nearest time interval you want. I’ll start by showing you what the calculations I used were, and then I’ll explain the theory behind it.
I. The calculations
To round to the nearest hour use:
DATETIME( INT ([DateTime]) + (ROUND( FLOAT([DateTime])%1*24 ) /24) )
To round to the nearest minute use:
DATETIME( INT ([DateTime]) + (ROUND( FLOAT([DateTime])%1*1440 ) /1440) )
As you might have noticed, the difference between the 2 calculations are the last 2 numbers used. This part allows you to specify the time interval you want to round to.
Hopefully in the following section, you can understand how these calculations work and be able to set the time interval to whatever you may need.
II. How Tableau treats dates and time
An essential first step to understanding these calculations is to understand how Tableau is understanding dates and time.
Tableau stores dates as simple numbers. An integer or a float counting the number of days since 01/01/1900. So on the date I am writing this, (07/05/2019), Tableau would store this date as ‘43590‘, which means it is 43590 days since 1st January 1900.
So how does it deal with units smaller than a day?
This is where decimals come into play.
12 hours is the equivalent of half a day, so Tableau stores this as ‘0.5‘.
That means if I want to store the datetime 07/05/2019 12:00:00, Tableau would record the number ‘43590.5‘, as in to say there have been 43590 and a half days since 1st January 1900 00:00:00.
Simple right? If you’re still following the logic then we can move on to the calcs.
III. Theory behind the calcs
When you have a Date or DateTime field type in Tableau, you can easily convert this into the number form using either INT([Date]) or FLOAT([DateTime]).
Luckily the reverse is also true, e.g. the expression: DATETIME(43590) will return 07/05/2019 00:00:00. That is how we start building the calc, now we just need to feed the correct number into it.
Let’s pretend we want to round 26/01/2019 14:56:21 to the nearest hour – so the output we want is 26/01/2019 15:00:00
DATETIME( INT ([DateTime]) + (ROUND( FLOAT([DateTime])%1*24)/24) )
INT([DateTime]): This section brings out the number of days since 01/01/1900 WITHOUT doing any rounding. It will just chop off anything after the decimal and pretend it never existed. In our example, it will return the value 43489. So now we need to add the correct fraction of a day i.e. the TIME part of the date that is rounded correctly.
DATETIME( INT ([DateTime]) + (ROUND( FLOAT([DateTime])%1*24 ) /24) )
FLOAT([DateTime])%1*24: There are 3 things that are happening here.
FLOAT(): First, the datetime is converted to a float which will include the time in the number (in our case 43489.622465).
%1: Second, we need to strip out everything BEFORE the decimal point. We can do this using the % operator which means ‘modulo’. Basically %1 means if I divide this number by 1, what remainder am I left with. (0.622465)
*24: Last, we need to multiply this by the time interval we care about. Since we are rounding to the nearest hour, we need to divide a day up into hour portions, which would be 24 as there are 24 hours in a day. (14.9392)
Now we have a number in the units of time interval we want. The time 14:56:21 is exactly 14.9392 hours.
Finally here is where we can round the number, so we wrap that entire expression with ROUND() which gives us 15.0.
Last but not least, we need to convert this value back into a fraction of one day. Divide by 24. (0.625)
So eventually this calculation becomes DATETIME(43489.625) which results in 26/01/2019 15:00:00. Tada!
If you want to change the time interval you want to round to, you need to break up a day into those components.
So if you wanted it to the nearest minute, you need to find how many minutes there are in a day.
24 hours * 60 minutes = 1440 minutes a day. So we would replace the 24 for 1440.
Say you wanted it to the nearest second:
24 hours * 60 minutes * 60 seconds = 86400 seconds in a day. So the 24 would be replaced with 86400.
You can change this for any time interval you wish!
Whew that was long! Hopefully you have learnt something useful today.