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

Question

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.

Erwin Brandstetter · Accepted Answer · 2016-05-18T17:02:26.790

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.

This is excellent, very good suggestion. However it opens up another question for me which is how to manage postgres functions in Rails. I've asked this as a separate question and would love to know your thoughts - http://stackoverflow.com/questions/20314724/how-do-you-define-postgres-functions-in-rails-and-what-is-their-scope-and-lifeti — Peter Nixey, Dec 01 '13 at 16:59

score 2 · Answer 2 · answered Oct 18 '14 at 10:12

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.

score 1 · Answer 3 · answered Sep 15 '21 at 04:54

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 timestamptz
AS $func$
    SELECT system_now() + INTERVAL '1225288 seconds';
$func$ LANGUAGE SQL;

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

Please note... this is not about stubbing Rails' time

3 Answers3