Time format Hour:min (to show every 15 min in dimension)

[sudeepkm]

Hi,

I've a date/time field (field name as Timestamp) in my Data Model.

Records of that field are like 2012-05-03 08:34:25, 2012-05-03 09:54:15, 2012-05-03 20:44:20 etc.

Currently i'm using time(frac(Timestamp),'h:mm tt') which is giving the value like 8:34 AM, 9:54 AM, 8:44 PM

I want to group them in every 30 min so that it shows 8:00 AM, 8:30 AM, 9:00 AM etc in my dimension.

Please help.

Thanks and Regards,

Sudeep

[m_woolf · Accepted solution]

Try this:

floor(frac(STARTTIME)*48)/48+floor(STARTTIME) as YourField,    // QV next lowest half hour (1/48 of day)

[sudeepkm]

Wow very quick. Thanks a lot.

It was returning in jullian format so I applied

time(floor(frac(STARTTIME)*48)/48+floor(STARTTIME),'h:mm TT') as YourField, and it retunrs 8:00AM, 8:30 AM etc as expected.

again thanks a lot.

Regards,

Sudeep