Is there a way to query the result of the startree-index for time periods? I guess this type of query is probably executed by ThirdEye?

Yes, you can query Pinot for time periods using any index not just star tree.

where can I find info on how to construct such a pinot-SQL query?

It would be like sql (where timeCol > x and timeCol < y), or using between as you would in sql