return native PostgreSQL ISO8601 format

[seven1240]

Environment

• PostgreSQL version: docker postgres:14.0-alpine
• PostgREST version: docker v11.2.0
• Operating system: Darwin Kernel Version 22.2.0 root:xnu-8792.61.2~4/RELEASE_ARM64_T6000 arm64

Description of issue

PostgreSQL by default returns ISO 8601 format withouth T between date and time for easy reading, e.g.

2023-08-25 21:50:45
v.s.
2023-08-25T21:50:45

But PostgREST has T in it. Looks like it was without T in 2015: #177

Anyway, is it possible to add a config option to choose the format?

Thanks.

[steve-chavez]

@seven1240 I always get the T inside the timestamp:

create table article_stars (
    article_id integer not null,
    user_id integer not null,
    created_at timestamp without time zone default now() not null,
    primary key (article_id, user_id)
);

curl localhost:3000/article_stars
[{"article_id":1,"user_id":1,"created_at":"2015-12-08T04:22:57.472738"}, 
 {"article_id":1,"user_id":2,"created_at":"2015-12-08T04:22:57.472738"}, 
 {"article_id":2,"user_id":3,"created_at":"2015-12-08T04:22:57.472738"}, 
 {"article_id":3,"user_id":2,"created_at":"2015-12-08T04:22:57.472738"}, 
 {"article_id":1,"user_id":3,"created_at":"2015-12-08T04:22:57.472738"}]

To remove it, you could use a computed field or domain representation.

---

For domain representations, this example could help:

postgrest/test/spec/fixtures/schema.sql

Lines 3128 to 3144 in 531a183

	DROP DOMAIN IF EXISTS public.isodate CASCADE;
	CREATE DOMAIN public.isodate AS timestamp with time zone;
	CREATE OR REPLACE FUNCTION isodate(json) RETURNS public.isodate AS $$
	SELECT isodate($1 #>> '{}');
	$$ LANGUAGE SQL IMMUTABLE;
	CREATE OR REPLACE FUNCTION isodate(text) RETURNS public.isodate AS $$
	SELECT (replace($1, 'Z', '+00:00')::timestamp with time zone)::public.isodate;
	$$ LANGUAGE SQL IMMUTABLE;
	CREATE OR REPLACE FUNCTION json(public.isodate) RETURNS json AS $$
	SELECT to_json(replace(to_json($1)#>>'{}', '+00:00', 'Z'));
	$$ LANGUAGE SQL IMMUTABLE;
	CREATE CAST (public.isodate AS json) WITH FUNCTION json(public.isodate) AS IMPLICIT;
	CREATE CAST (json AS public.isodate) WITH FUNCTION isodate(json) AS IMPLICIT;

[steve-chavez]

Additionally you could try changing the datestyle for the authenticator:

alter role authenticator set datestyle to postgres;

[seven1240]

Hi @steve-chavez , thank you for the detailed explain. The domain way seems is little complicated.

Additionally you could try changing the datestyle for the authenticator:

alter role authenticator set datestyle to postgres;

I tried this and no effect to me even after I restarted PostgREST. Also, postgres is not the right style I want. That's what I saw in psql:

xui=> show datestyle;
 DateStyle
-----------
 ISO, MDY
(1 row)

xui=> select now();
              now
-------------------------------
 2023-08-30 22:24:22.917144+08
(1 row)

xui=> set datestyle = postgres;
SET
xui=> select now();
                 now
-------------------------------------
 Wed Aug 30 22:24:31.516747 2023 CST
(1 row)

According to https://www.postgresql.org/docs/current/datatype-datetime.html :

ISO 8601 specifies the use of uppercase letter T to separate the date and time. PostgreSQL accepts that format on input, but on output it uses a space rather than T, as shown above. This is for readability and for consistency with RFC 3339 as well as some other database systems.

So back in the original question, Looks like PostgREST in a 2015 version has not T and does match what we see in psql as described in #177 . What changed from then?

I guess the actual format is still formatted by PostgREST, or the low level pg client lib?

Thanks again.

[wolfgangwalther]

This format is used when returning a timestamp as json:

postgres=> SELECT to_json(now());
              to_json
------------------------------------
 "2023-08-30T15:04:48.724897+00:00"
(1 row)

Since we use the built-in transformation to json now, all columns exposed as type timestamp will have this format. However, if you cast your columns to text first, you will receive what you want:

postgres=> SELECT to_json(now()::TEXT);
             to_json
---------------------------------
 "2023-08-30 15:04:44.512372+00"
(1 row)

Datestyle settings will only affect the cast to TEXT (2nd example), not the cast to json directly.

You can achieve the cast either via VIEWs, via computed columns (as mentioned above) or via domains (also mentioned above).