Secondary Time Dimensions

Hey guys,

I’m curious what people have found to be the best way to deal with filtering on and grouping by secondary time dimensions. There seem to be multiple possible solutions, but I’m not sure which would be the right choice with regards to query load.

Thanks!

Michael

Hey Michael,

I think ideally you’d want a long dimension, but since that isn’t possible, the best thing is probably a string column that is just the millis.toString, or an ISO8601 representation. Then you’d use extraction functions to get the grouping you want. millis.toString should be faster if it works (it should be faster to parse) but Im not totally sure if it does work.

Thanks Gian, are the extraction functions usable for filtering as well or is that something I would need to use javascript filtering for?

You can use extraction filters: http://druid.io/docs/latest/querying/filters.html

Haha whoops, I was on the 8.1 docs instead of latest. Thanks!

{ “type”:“extraction”, “dimension”: “install_date”, “value”:“2015-09-20”, “extractionFn”: {“type”: “time”, “timeFormat”: “yyyy-MM-dd HH:mm:ss”, “resultFormat”: “yyyy-MM-dd”}} as a filter is giving me a null exception. Is the syntax okay? I was able to get the time parsing extraction function to work as dimension.

Thanks,

Michael

Hi Michael, can you post the full exception stack trace?

Sure thing, probably going to be a little later this weekend. I think I might have missed on Gian’s suggestion though, as Time Parsing Extraction Function seems like it wouldn’t be great for filtering on a timerange especially when timezone comes into play. Gian/FJ did you mean javascript extraction function on millis based on a range of millis?

Thanks,

Michael

Hey Michael,

Yeah, time extractions for filtering wouldn’t be great if you need a range, only if you wanted to filter on specific time buckets. (they should work for filtering on specific buckets)

If you do need a range filter, the JavaScript filters are probably the best bet.