EXPLAIN ANALYZE
SELECT *
FROM food
WHERE food.post_timestamp >= ('now'::date - interval '1 month')::date AND food.post_timestamp <= 'now'
ORDER BY food.post_timestamp DESC
LIMIT 30;
I think the problem is that you're using 'now'::date in your first example, which gets frozen. You can use now() or current_timestamp or current_date to get dynamic results.
CREATE VIEW test_now AS SELECT current_timestamp as current_ts, now() as now_function,'now'::timestamp AS now_literal;
(wait a couple of seconds)
SELECT * FROM test_now;
current_ts | now_function | now_literal
-------------------------------+-------------------------------+----------------------------
2014-08-21 01:25:54.147004-07 | 2014-08-21 01:25:54.147004-07 | 2014-08-21 01:18:22.207073
(1 row)
You'll see that the last column is frozen while the other two stay current.
Cheers,
Ken