Rounding Time Values
Altair EmployeeAnswers
-
Hi Raymond,
As you likely know dates/times are commonly stored as a numeric value for seconds since a known reference base date.
So one way or another the likely best approach for generic use may be to use a calculated field to convert the date/time to seconds, work out whether the remainder value for a 30 minute period should round up or down and adjust accordingly and use the new value to write the new date/time or which ever part of it you need.
You could approach this in a number of ways and, of course, if you already have the time extracted as a number of seconds into the day (with one eye on time zones perhaps?) then you only need to look at the number of elapsed seconds in the day or, more specifically for your 30 minute grouping example, the remainder value after you have divided the number of seconds by the number of seconds in 30 minutes.
Depending in which version and variant of Monarch you are using there may well be some alternative approaches available from built in functions but if you want to create your own function for your specific needs (and save it for re-use as required) this is the approach that is likely to be the most portable.
HTH.
Grant
0 -
Depending on how you want to round to the nearest half hour, i.e., Minutes 0-29 -> 0, and Minutes 30-59 -> 30, or Minutes 0-14 -> 0 of the current hour, Minutes 15-44 -> 30 of the current hour, Minutes 45-59 -> round up to the next hour, here is one possibility using Monarch Classic v15 (but this should work with any version of Monarch or Data Prep that support the functions) for the first case.
I created a few formula fields to make it easier to read, but you can always combine this into one long formula. In this example I am going to round a date/time field called "Stop Time" which has the format YYYY-MM-DD HH:mm:ss.
Stop Time
2017-03-23 23:30:35
2017-03-23 23:32:12
2017-03-23 23:37:33
2017-03-24 01:40:48
2017-03-24 03:03:45
2017-03-24 13:12:24
- Create a numeric formula field called "Minute"
- minute([Stop Time])
- Create a numeric formula field called "Floor"
- floor(Minute,30)
Now for the fun. We are going to pull the individual parts of the "Stop Time" field to build a string and then convert it back to a date time
- Create a date/time field with the format of Short date & time
- ctod(dtoc(date([Stop Time])) + " " + str(hour([Stop Time]),2,0,"0") + ":" + str(floor,2,0,"0"))
The above formula converts the date "Stop Time" to a character string and concatenates a space, followed by the hour portion of "Stop Time" converted to a string (the hour function returns a numeric), followed by a colon and the value of the "Floor" field converted to a string. The entire result is then converted back into a date field.
Hope this helps.
0 - Create a numeric formula field called "Minute"
-
Altair Forum User said:
Hi Raymond,
As you likely know dates/times are commonly stored as a numeric value for seconds since a known reference base date.
So one way or another the likely best approach for generic use may be to use a calculated field to convert the date/time to seconds, work out whether the remainder value for a 30 minute period should round up or down and adjust accordingly and use the new value to write the new date/time or which ever part of it you need.
You could approach this in a number of ways and, of course, if you already have the time extracted as a number of seconds into the day (with one eye on time zones perhaps?) then you only need to look at the number of elapsed seconds in the day or, more specifically for your 30 minute grouping example, the remainder value after you have divided the number of seconds by the number of seconds in 30 minutes.
Depending in which version and variant of Monarch you are using there may well be some alternative approaches available from built in functions but if you want to create your own function for your specific needs (and save it for re-use as required) this is the approach that is likely to be the most portable.
HTH.
Grant
Didn't realize you responded as I was typing up my idea. Just goes to show there is always multiple ways of achieving the same result in Monarch.
0 -
Altair Forum User said:
Didn't realize you responded as I was typing up my idea. Just goes to show there is always multiple ways of achieving the same result in Monarch.
Hi Chris,
As we say in the UK, it's like waiting for a bus. You wait ages and suddenly two turn up at the same time!
You are quite right of course. One of the interesting aspects of Monarch has always been that there are usually several ways to approach the more complex requirements and in general all of them will work well.
With dates/times one just needs to bear in mind the possible complexities of whether the adjustment or categorization might move a value into a different day for reporting purposes - especially if Time Zone adjustments are also a factor.
If the requirement is clearly not likely to be affected by either of those considerations, now or in the future, there are a number of options available and for this example you have covered some of them (probably the best imo) comprehensively.
But many years ago I was working with systems that were heavily date and time performance analysis related and needed to cover time zone adjustments too and that left me with a tendency to go back to Date Serial Number based calculations just to allow for those rare transactions that crossed date based reporting boundaries without having to create special "just in case" based formulas every time there was a possibility that the date might not be kept in alignment with selection criteria after a time grouping activity was undertaken.
I suspect that in this example since only minutes are being assessed there should not be a problem.
However, if a time is rounded from, say, 00hr 14m to 00hr 00m it would be grouped with data for the previous DATE that was rounded UP from 23hr 46min and set to 00:00hr 00m (Presumably) which also implied a Date change for one side or the other. So one needs to be clear about reporting in this particular half hour "bucket" if report selection criteria include the date of the base transactions rather the "bucket" date.
Just an example of something for which a policy may be required and made know to users.
Grant
0 -
Altair Forum User said:
Depending on how you want to round to the nearest half hour, i.e., Minutes 0-29 -> 0, and Minutes 30-59 -> 30, or Minutes 0-14 -> 0 of the current hour, Minutes 15-44 -> 30 of the current hour, Minutes 45-59 -> round up to the next hour, here is one possibility using Monarch Classic v15 (but this should work with any version of Monarch or Data Prep that support the functions) for the first case.
I created a few formula fields to make it easier to read, but you can always combine this into one long formula. In this example I am going to round a date/time field called "Stop Time" which has the format YYYY-MM-DD HH:mm:ss.
Stop Time
2017-03-23 23:30:35
2017-03-23 23:32:12
2017-03-23 23:37:33
2017-03-24 01:40:48
2017-03-24 03:03:45
2017-03-24 13:12:24
- Create a numeric formula field called "Minute"
- minute([Stop Time])
- Create a numeric formula field called "Floor"
- floor(Minute,30)
Now for the fun. We are going to pull the individual parts of the "Stop Time" field to build a string and then convert it back to a date time
- Create a date/time field with the format of Short date & time
- ctod(dtoc(date([Stop Time])) + " " + str(hour([Stop Time]),2,0,"0") + ":" + str(floor,2,0,"0"))
The above formula converts the date "Stop Time" to a character string and concatenates a space, followed by the hour portion of "Stop Time" converted to a string (the hour function returns a numeric), followed by a colon and the value of the "Floor" field converted to a string. The entire result is then converted back into a date field.
Hope this helps.
This is exactly what I was trying to achieve, thank you!
0 - Create a numeric formula field called "Minute"