let me summarize • Presto has datetrunc udf • Pinot has datetimeconvert udf • When you query via presto you can use either datetrunc or datetimeconvert, when you use datetrunc presto-pinot connector converts it into datetimeconvertudf automatically • When you query via pinot, you can only use datetimeconvertudf

Cool

Are there any performance tips related to doing date/timestamp filters with Pinot? I want to make sure I don't do weird things in Presto that hurts latency.

I'm looking at TimeZone support.

yes, its better to use epoch

while storing data in Pinot

and if possible do the tz conversion in client side

How do I modify the time in date_trunc? I run the following query to do a report in UTC but I think date_trunc is rendering in LosAngeles. The Pinot docs seem out of sync with the code.

presto> select platform_id, date_trunc('day',"timestamp") as "date", sum(clicks) as clicks, sum(impressions) as impressions, sum(cost_usd_micros) as cost_usd_micros from pinot.default.events_testing where platform_id = 1 and "timestamp" >= from_iso8601_timestamp('2020-02-01T00:00:00.000Z') and "timestamp" < from_iso8601_timestamp('2020-02-03T00:00:00.000Z') group by platform_id, date_trunc('day',"timestamp") order by date asc, platform_id asc limit 101;
 platform_id |          date           | clicks | impressions | cost_usd_micros 
-------------+-------------------------+--------+-------------+-----------------
           1 | 2020-01-31 00:00:00.000 |      0 |           1 |               0 
           1 | 2020-02-01 00:00:00.000 |      1 |           1 |           85791 
           1 | 2020-02-02 00:00:00.000 |      0 |           5 |               0

https://docs.pinot.apache.org/users/user-guide-query/pinot-query-language https://github.com/prestodb/presto/blob/78f24709b80f17bc7b7f6a0317cd9ee0515c638e/presto-pinot-toolkit/src/main/java/com/facebook/presto/pinot/query/PinotAggregationProjectConverter.java#L100

I'll probably support a couple levels of timezones. I think when we chatted previously, it made sense to introduce other date columns in the appropriate timezone.

How does the time field interact with the star-tree-index? Should I include the time field in my index like any other field? If I only need date-level data externally, should my extra timestamp columns actually just have the local dates in the timezones I need?

if there is a udf on the time column, star-tree will be de-activated at query time

yes, include the time field as it

better to create multiple time columns to get benefit of star tree

time_ms, time_h, time_d, time_w, time_m

Cool, sounds good. How about an int for day? E.g. 20200503?

yeah, that can work

my suggestion is to use ms (long) for everything

but round it to nearest day

Interesting. Why?

Local date can then be represented using an int32.

Eventually, we plan to add intelligence in query layer to use the right column

Based on the query and granularity requested, we can pick the right column

Ah, I see.

Having them in same format (ms) will help achieve this

Makes sense. It'll probably be a month before I improve TimeZone support (supporting multiple levels). I'll sync up then.

Okay

@User has left the channel

@User has left the channel

@User has left the channel

@User has left the channel