Need unique values from time field

[allann]

We are extracting time intervals from a timestamp field. We would like to use this as a filter but the values are repeating. We only need a list of intervals from 12:00 am to 11:30 pm, so the list should only be 24 values. 

Load script currently:  Time(DateTime) as DateTimeIntervals

[vinieme12]

load distinct hour(Time(DateTime) ) as DateTimeIntervals

[allann]

seems like that's the right track, but it gives me 0 to 23. actually the intervals are 30 min so the list should be 48 values.

i need it to show:

12:00 am

12:30 am

1:00 am

1:30 am and so on

[edwin]

this means your field have values that are more granular.  remember time is a dual, the number portion may be more granular than what you are displaying that may be why you are seeing duplicates though its not really dups as the number is different.  you may want to create a new field that is rounded off to the time intervals you need

[allann]

that's what i was wondering. it didn't make sense to me that they weren't aggregating. let me look in to that.

[MarcoWedel]

Time(Floor(DateTime,'00:30:00')) as DateTimeIntervals

or

Time(Floor(DateTime,1/48)) as DateTimeIntervals

[MarcoWedel · Accepted solution]

you maybe have to purge the date part using frac() as well:

Time(Floor(Frac(DateTime),1/48)) as DateTimeIntervals

[allann]

thanks, this is what i was looking for!

[allann]

Maybe I spoke to soon. It's working but when I choose an interval like 9:30PM, I'm seeing 9:30PM and 10PM. 

[MarcoWedel]

please post some sample data that shows this effect