Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I would like to round time this way :
From original timestamp | Half hour | Quarter | Time by 5 minutes steps |
---|---|---|---|
21:18:00 | 21:00 | 21:15 | 21:15 |
21:39:00 | 21:30 | 21:30 | 21:35 |
21:56:00 | 21:30 | 21:45 | 21:55 |
Rounded values should never be superior to originale timestamp.
So possible values can be :
* Half hour -> 21:00, 21:30, 22:00, 22:30 ...
* Quarter -> 21:00, 21:15, 21:30, 21:45 ...
* Time by 5 minute step -> : 21:00, 21:05, 21:10, 21:15 ...
I created a master calender this way (Field "Horodatage" is a timestamp) :
Calendrier:
NoConcatenate
LOAD Distinct
Horodatage, // Original timestamp
MakeDate(Year(Horodatage), Month(Horodatage), Day(Horodatage)) as Date,
Num(Hour(Horodatage), '00') & 'h' as Hour,
Time(Class(Time(Horodatage),0.5/24), 'hh:mm') as Half_Hour,
Time(Class(Time(Horodatage),0.5/2/24),'hh:mm') as Quarter,
Time(Class(Time(Horodatage),0.5/6/24),'hh:mm') as Time_By_5
Resident Calendrier_TMP;
But i recently noticed that sometimes rounding is correct and sometimes not (yellow):
Do you have an idea why it doesn't work here? Do you have a better calculation method ?
Thank you by advance !
Perhaps:
Time(Ceil(frac(Horodatage),1/48)) as Half_Hour
Time(Ceil(frac(Horodatage),1/96)) as Quarter_Hour
You could do like this:
Time(Round(MyTime, (1/24/12))) as [5 minutes],
Time(Round(MyTime, (1/24/4))) as Quarter,
Time(Round(MyTime, (1/24/2))) as HalfHour
You can use Round,Floor or Ceil depending on your needs.
//Martin
LOAD *,
Time(Floor(Frac(Horodatage),'00:30:00')) as Half_Hour,
Time(Floor(Frac(Horodatage),'00:15:00')) as Quarter,
Time(Floor(Frac(Horodatage),'00:05:00')) as Time_By_5
Inline [
Horodatage
02/01/2014 21:18:00
02/01/2014 21:39:00
02/01/2014 21:56:00
];
regards
Marco