How can I set the time in postgres in order to test timezone behaviour using RSpec?

Postgres uses the date / time setting of the underlying OS (at least on UNIX-like systems). To stage various timestamps, you would have to set the time of the OS, with the date command.

I would certainly not recommend that on a system that does anything else of importance. cronjobs can run haywire and other unpleasant side effects.

Instead, replace the function now() with a user-defined server-side function like:

CREATE OR REPLACE FUNCTION now_test()  RETURNS timestamptz AS $func$SELECT '2013-12-01 12:34'::timestamptz$func$ LANGUAGE SQL;

(The above cast assumes the current time zone setting of the session. Alternatively, you can provide a time zone or time offset with the literal.)
Then you can test your code with:

Schedule.where('evening_reminder_last_sent_on_local_date !=                              DATE(now_test() AT TIME ZONE time_zone)')

Modify the above SQL function above with various timestamps and you are good to go.

The previous suggestion is indeed very good but as I wanted to easily test my query with Delorean gem, I came up with this workaround:

Schedule.where('evening_reminder_last_sent_on_local_date !=                DATE( ?::timestamp AT TIME ZONE time_zone )', Time.now)

I took the previous example just for the sake, but I had to manipulate times in Postgres with the now() function. I couldn't just inject my ruby time instead without casting it with ::timestamp.

Moreover, in your case, maybe you could use timestamptz.

I had a similar case except instead of modifying the timezones, I wanted to apply an offset. This let me synchronize postgres's responses to NOW() with my appliction's responses to datetime.now() (tampered via libfaketime).

I ended up with one statement that renamed now() to system_now(), but only if system_now() didn't already exist:

DO $$    DECLARE    found_func pg_proc%rowtype;    BEGIN    SELECT * FROM pg_proc WHERE proname = 'system_now' INTO found_func;    IF FOUND THEN        RAISE NOTICE 'DB Already Faked';    ELSE        RAISE NOTICE'Faking DB Time';        ALTER FUNCTION now RENAME TO system_now;    END IF;END $$;

...and another one that redefined NOW() to include the offset:

CREATE OR REPLACE FUNCTION now() RETURNS timestamptzAS $func$    SELECT system_now() + INTERVAL '1225288 seconds';$func$ LANGUAGE SQL;