You are correct that date_trunc will not utilize an index. To the query planner that's an opaque function; it has no idea how to translate that to an index con

You are correct that date_trunc will not utilize an index. To the query planner that's an opaque function; it has no idea how to translate that to an index condition. However, the example you gave works just fine. It's all just UTC in backend and very much deterministic. postgres=# create table timetest as select t.time, 'hi' as foo from generate_series('2000-01-01'::timestamptz, '2010-01-01'::timestamptz, '5 minutes'::interval) t(time); SELECT 1052065 postgres=# create index on timetest(time); CREATE INDEX postgres=# explain analyze select * from timetest where time > now() - '30 days'::interval; ┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ Index Scan using timetest_time_idx on timetest (cost=0.43..4.45 rows=1 width=11) (actual time=0.019..0.019 rows=0 loops=1) │ │ Index Cond: ("time" > (now() - '30 days'::interval)) │ │ Planning Time: 1.163 ms │ │ Execution Time: 0.081 ms │ └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ If you query frequently with date_trunc, you may want to use an expression index: postgres=# create index on timetest(date_trunc('day', time at time zone 'US/Pacific')); CREATE INDEX postgres=# explain analyze select * from timetest where date_trunc('day', time at time zone 'US/Pacific') > now() - '30 days'::interval; ┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ Index Scan using timetest_date_trunc_idx on timetest (cost=0.43..4.45 rows=1 width=11) (actual time=0.075..0.076 rows=0 loops=1) │ │ Index Cond: (date_trunc('day'::text, timezone('US/Pacific'::text, "time")) > (now() - '30 days'::interval)) │ │ Planning Time: 0.379 ms │ │ Execution Time: 0.124 ms │ └───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ Note that to make this work you have to convert "time" to a timestamp (no tz), because date_trunc with timestamptz is not an immutable function and cannot be indexed—I believe this is what you may have been thinking of when you say that timestamptz is non-deterministic.