CODEWITHSUNDEEP
postgresqldockertimescaledb
Scott Clark
Scott Clark

Reputation: 628

Postgres Running in Docker Container: Change current time, now() to custom date time

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?

Upvotes: 0

Views: 1037

Answers (1)

Sven Klemm
Sven Klemm

Reputation: 436

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

Upvotes: 2

Related Questions