How To create a calculated dimension using timestamp intervals

[rsark]

Hello,

I am attempting to create a bar chart with a calculated dimension that shows 30 minute increments using a timestamp field. I attempting to use the class function - class(minute(timestamp), 30) and I am not having any luck. Any help/suggestions would greatly be appreciated. Thanks

dimension result example: 6:00 AM - 6:30 AM, 6:31 - 7:00 AM, 7:01 - 7:30, etc

[johnw]

First problem is that the minute() function returns an integer between 0 and 59. So your classification just gives you the first half of each hour vs. the second half of each hour.

It would be a simple matter to extract the number of minutes from the start of the day, timestamp*24*60, so you could do class(timestamp*24*60,30). You then have the right groups, but they'll display poorly, such as 960 <= x < 990.

You could instead stick with timestamps, like class(timestamp, 30/(24*60), but that looks even worse, as it shows the fraction of the day, not a timestamp format.

You can do a timestamp format, like timestamp(class(timestamp,30/(24*60)),'h:mm TT'), but then it only shows the starting time, not the ending time. Still, maybe that's good enough.

To get exactly what you're asking for, though, it looks like this does the trick:

=dual(timestamp(class(timestamp ,30/(24*60)),'h:mm TT') & ' - ' &

timestamp(class(timestamp+30/(24*60),30/(24*60)),'h:mm TT')

, class(timestamp ,30/(24*60)))

There might be a simpler way, but that's what I came up with.

You might be better off, though, calculating that in the script rather than at run time in a chart.

[Anonymous]

Speaking of simpler way... If you have Date field in you application, you can use
=Date/48

[johnw]

Put as simply as possible, since when does a date tell you what time it is?

Dividing a date by 48 turns 48 days into a single day, with each day being half an hour. That... makes no sense, and has nothing to do with solving this problem, unless I'm VERY confused.

[rsark]

Hi John,

Thanks for the quick reply. I found the expression you provided was very close to what I need, but it seems to be giving a result for each row. I would like to see the result with only 48 possible rows. Thanks in advance for your assistance/reply

[johnw · Accepted solution]

*smacks forehead* Ah, of course. I forgot about the date portion of the timestamp. We can eliminate it with frac(timestamp). Again, not sure this is the best way, but it seems to work. See attached.

=dual(timestamp(class(frac(timestamp) ,30/(24*60)),'h:mm TT') & ' - ' &

timestamp(class(frac(timestamp)+30/(24*60),30/(24*60)),'h:mm TT')

, class(frac(timestamp) ,30/(24*60)))

[rsark]

Thanks,

The solution John provided worked. I added the expression in resident table load and linked back to timestamp field.

[rsark]

Thanks,

The solution John provided worked. I added the expression in resident table load and linked back to timestamp field.

[Anonymous]

John, you're right.
I didn't pay attention that it's about intervals.

[johnw]

Here's a slightly simpler expression that seems to do the same thing:

=dual(time(floor(timestamp, 1/48),'h:mm TT') & ' - ' &

time(ceil (timestamp, 1/48),'h:mm TT')

,floor(frac(timestamp),1/48))