Postgres has some really nice keywords for dealing with typical dates (today, yesterday, tomorrow). One problem I stubled about was using these in views.

select 'yesterday'::date;

provides yesterday's date, e.g.:

    date
------------
 2017-04-18
(1 row)

Assume, you want to use this in a view:

create view yesterday_v1 as select 'yesterday'::date;

If you have a close look at the view, you will realize, this view probably won't do what you expect:

\d+ yesterday_v1
            View "public.yesterday_v1"
 Column | Type | Modifiers | Storage | Description
--------+------+-----------+---------+-------------
 date   | date |           | plain   |
View definition:
 SELECT '2017-04-18'::date AS date;

This view will always result in the same date. To use "yesterday" in a view, you need to define the view a little different:

create view yesterday_v2 as select cast('' || 'yesterday' ||'' as date);

If you create the view that way, the definition looks differently:

\d+ yesterday_v2
            View "public.yesterday_v2"
 Column | Type | Modifiers | Storage | Description
--------+------+-----------+---------+-------------
 date   | date |           | plain   |
View definition:
 SELECT ((''::text || 'yesterday'::text) || ''::text)::date AS date;