11 Replies Latest reply on Aug 20, 2018 8:53 PM by elias.daniels.0

# Rounding time down to nearest 30 minute Interval

Hello,

Does anyone know how to round down to the nearest 30 minute interval with date and time stamps?  The date portion is irrelavant, I just need to know how many transaction happed within each half hour interval.

Attached is sample data.

thanks,

Greg

• ###### 1. Re: Rounding time down to nearest 30 minute Interval

This is not exactly what you need, but it could help.

The attached workbook shows in Sheet 2 the frequency of transactions per hour.

Best,

Ramon

• ###### 2. Re: Rounding time down to nearest 30 minute Interval

Greg,

This will create 48 30minutes blocs.

ROUND

(

((DATEPART('hour', [start_time (copy 2)] ) * 60)  +  DATEPART('minute', [start_time (copy 2)] ))

/ 30

)

You could add a Case  to  name each bloc if you wish.

I changed the type of the start_time  to a Date_Time.

Michel

1 of 1 people found this helpful
• ###### 3. Re: Rounding time down to nearest 30 minute Interval

Michel's solution might work for you, Just adding one more option with AM/PM format.

Str(DATEPART('hour',DATETIME([start_time (copy 2)]))) + ':' +

Str(If DATEPART('minute',DATETIME([start_time (copy 2)]))>=0

and (DATEPART('minute',DATETIME([start_time (copy 2)]))<30 ) then '00' else '30' End) +

(If DATEPART('hour',DATETIME([start_time (copy 2)]))<12 then ' AM' Else ' PM' End)

output 2 of 2 people found this helpful
• ###### 5. Re: Rounding time down to nearest 30 minute Interval

Hi Indumon,

Thanks for sharing, that is nice solution to Greg's requirement.

Ramon

• ###### 6. Re: Rounding time down to nearest 30 minute Interval

Actually, .Indumon solution is cleaner , simplier and better to precisely set the boundaries    compared to my solution.

I`ll have a look at what Richard just added.

Michel

• ###### 7. Re: Rounding time down to nearest 30 minute Interval

thanks @richard.

Your solution is very straight forward and simple. Calculation became just a one liner now

DATETIME(INT(FLOAT([datetime]) * ([Units per Day] / [N])) / ([Units per Day] / [N]))

[datetime] - A datetime (or date) dimension

[Units per Day] - The number of the chosen unit in a day (i.e. Day = 1, Hour = 24, Minute = 1,440, Second = 88,400)

[N] - The number of units per bin

DATETIME(INT(FLOAT(datetime([start_time (copy 2)])) * (1440 / 30)) / (1440 / 30))

• ###### 8. Re: Rounding time down to nearest 30 minute Interval

This works for me:

Str(

If DATEPART('hour',DATETIME([occurred_at]))>= 13 then DATEPART('hour',DATETIME([occurred_at]))-12 Else DATEPART('hour',DATETIME([occurred_at])) End)

+

':'

+

Str(If DATEPART('minute',DATETIME([occurred_at]))>=0

and (DATEPART('minute',DATETIME([occurred_at]))<30 ) then '00' else '30' End)

+

(If DATEPART('hour',DATETIME([occurred_at]))<12 then ' AM' Else ' PM' End)