Creating additional field column by Time of day

[#1]

I have a data set that I have been able to break down a single column into multiple fields from the load script below.

I now have the ability to drill down by Year, Month, Day, and Time.

My problem is that my Time field contains every time stamp down to the second.

Does anyone know the easiest way to group the Time data so so I can see the results at a specific hour or half hour?

Ideally, I would like time to read

1PM

2PM

3PM

4PM

5PM

etc.

Directory;

LOAD

Year([RequestTime]) as Year,

Month([RequestTime]) as Month,

Day([RequestTime]) as Day,

Time([RequestTime]) as Time;

LOAD

Timestamp(Timestamp#(Mid([RequestTime],2,20),'DD/MMM/YYYY:hh:mm:ss')) as [RequestTime]

FROM

[..\..\20140313_Resource_2.xlsx]

(ooxml, embedded labels, table is resource);

LOAD

[HTTP Status],

[Time to Serve Request (Microseconds)],

FROM

[..\..\20140313_Resource_2.xlsx]

(ooxml, embedded labels, table is resource)

[swuehl]

Try something like

Time(round([RequestTime],1/24),'hh TT') as Time;

[#3]

Am I able to break this down by every 5 minutes?

[swuehl · Accepted solution]

Above will round to the nearest hour of the day (24 hours a day, thus 1/24), if you want to round to nearest 5 minutes, there are 12 five minutes intervals per hours, so you need to use

Time(round([RequestTime],1/(24*12)),'hh:mm TT') as Time