topic Re: Need unique values from time field in App Development

Need unique values from time field

allann — Fri, 06 May 2022 19:00:09 GMT

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

Re: Need unique values from time field

vinieme12 — Fri, 06 May 2022 19:16:37 GMT

load distinct hour(Time(DateTime) ) as DateTimeIntervals

Re: Need unique values from time field

allann — Fri, 06 May 2022 19:37:44 GMT

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

Re: Need unique values from time field

edwin — Fri, 06 May 2022 19:52:27 GMT

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

Re: Need unique values from time field

allann — Fri, 06 May 2022 19:56:01 GMT

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

Re: Need unique values from time field

MarcoWedel — Fri, 06 May 2022 21:03:32 GMT

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

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

Re: Need unique values from time field

MarcoWedel — Sat, 07 May 2022 09:30:13 GMT

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

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

Re: Need unique values from time field

allann — Mon, 09 May 2022 18:23:25 GMT

thanks, this is what i was looking for!

Re: Need unique values from time field

allann — Tue, 10 May 2022 17:44:02 GMT

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

Re: Need unique values from time field

MarcoWedel — Tue, 10 May 2022 20:56:24 GMT

please post some sample data that shows this effect

Re: Need unique values from time field

allann — Tue, 10 May 2022 21:06:37 GMT

Re: Need unique values from time field

allann — Thu, 12 May 2022 15:29:19 GMT

replacing frac with num corrects the interval issue but it repeats again.