How can I set the time in postgres in order to test timezone behaviour using RSpec?
postgresql, rspec, ruby-on-rails
Solution
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.
Problem
I want to be able to test the behaviour of a scheduler component across different timezones. However, the functionality to trigger scheduled behaviour uses time based queries within postgres: e.g. ``` # find reminders which have not been sent for "today" in the local date Schedule.where('evening_reminder_last_sent_on_local_date != DATE( NOW() AT TIME ZONE time_zone )') ``` I would like to be able to test this behaviour in RSpec and ensure that it plays correctly through the day and that if I send a Japanese user a reminder at 1am UTC on the 25th Dec, then at 10pm UTC, their reminder for "today" will show up as not have been sent (since it's about 7am the next day in Japan). However, in order to do this I need to be able to set the datetime in postgres. Is this possible? Please note... this is not about stubbing Rails' time The challenge is not to stub the time in Rails - I know how to do that. The problem is how to set the time in Postgres.