Age function computes differently when used through a view in PostgreSQL FDW

Question

I have a view which has a column for age (like Age (CURRENT_TIMESTAMP, birth_dtm)). This works fine, but when used through a FDW offsets the age by current timezone's offset from GMT. PostgreSQL documentation states that postegersql_FDW sets the TIMEZONE to UTC. How do we work around it?

Consider the following table definition, the data in the table and a view based on the table:

CREATE TABLE test
(
    id      serial  PRIMARY KEY,
    birth_dtm   TIMESTAMP
);
INSERT INTO test (birth_dtm) VALUES ('2024-03-22 08:23', '2024-03-07 13:19', '2024-03-24 01:06);
CREATE VIEW view_test AS
    SELECT  id, birth_dtm, AGE (current_timestamp, birth_dtm) c_age
    FROM    test;

Now when we execute the statement in a foreign database where this VIEW is available:

SELECT *, AGE (current_timestamp, birth_dtm)
FROM    FDB.view_test;

We get the following output:

id	birth_dtm	c_age	age
1	2024-03-22 08:23	20 days 07:31:44.651565	20 days 13:01:44.651323
2	2024-03-07 13:19	1 mon 4 days 02:35:44.651565	1 mon 4 days 08:05:44.651323
3	2024-03-24 01:06	18 days 14:48:44.651565	18 days 20:18:44.651323

So my question is how to achieve the Output in Column 3 (view_test.c_age) as shown in Column 4?

Note that the difference is 5.30 between column 3 and column 4 is as per TIME ZONE Asia/Kolkata.

Answer 1

The cause of the problem is the questionable choice of the data type timestamp without time zone for birth_dtm. timestamp with time zone would have been the appropriate data type.

Since you (or somebody else) chose timestamp, the timestamp is time zone unaware, and the interpretation will vary according to the time zone of the reader. But a birth is an absolute event, so it must be that the time zone is implicitly assumed. If, for example, the database is in India and stores only Indian births for an Indian application, one could choose timestamp and tacitly assume that all timestamps are understood to be in the Indian time zone.

In that case, you should change your view definition to reflect that assumption. Otherwise, the data returned will depend on the time zone of the observer. If all your timestamps are in Indian time, the view definition should be

CREATE VIEW view_test AS
SELECT id, birth_dtm,
       AGE (current_timestamp, birth_dtm AT TIME ZONE 'Asia/Kolkata') c_age
FROM test;