0

I have a postgres timescale database running in docker. For the purposes of api testing I want SELECT NOW() to return lets say 2010-12-01 23:00:44.851242 +00:00. Basically every time I start up the container I want it to think the current date is some time in December 2010.

How can I achieve this? I cant seem to find any command to set current time in postgres. Do I need to change the system time in the docker container before the database shows up? Is that even something I can do?

Scott Clark
  • 608
  • 6
  • 21
  • 1
    Hi Scott I found a heap of suggestions in this Stack Overflow question in case any of these work for you. https://stackoverflow.com/questions/29556879/is-it-possible-change-date-in-docker-container I think Postgres would use the server time? – greenweeds Jul 28 '22 at 15:38
  • @greenweeds a clever library that does not work easily with timescaledb docker images. But with enough hacking it may one day work. This library is difficult to work with in docker due to semaphore/memory lock issues. – Scott Clark Jul 28 '22 at 21:23
  • Ah, fair enough. Thanks for letting me know! – greenweeds Jul 29 '22 at 09:24

1 Answers1

1

You can achieve this by creating a custom now() function in a separate schema and then adjusting the search_path to prefer that function over the builtin now function:

CREATE SCHEMA test;
CREATE OR REPLACE FUNCTION test.now() RETURNS timestamptz LANGUAGE SQL AS $$ SELECT '2000-01-01 0:00'::timestamptz; $$;
SET search_path TO test,pg_catalog,public;
-- make search_path change permanent for a specific user
ALTER USER <testuser> SET search_path TO test,pg_catalog,public;

SELECT now();
          now
------------------------
 2000-01-01 00:00:00+01
(1 row)

Time: 1.826 ms
Sven Klemm
  • 376
  • 1
  • 4
  • This is very clever. However I think its unfortunate we may have to do something like this. It also does not work. at least not with the timescale docker containers. If I have the above sql execute with all of our other scripts this just does not last. I run it all together and I get 2000-01-01 00:00:00+01 once. all other calls give me regular system time. This also does not cover all other cases where select current_timestamp is used. – Scott Clark Jul 28 '22 at 21:21
  • 1
    To make this last you have to make the search_path stick. For a specific user you can do it like so: ```ALTER USER SET search_path TO test,pg_catalog,public;``` – Sven Klemm Jul 29 '22 at 05:50