Search code examples
jsonpostgresqlstring-formatting

Timestamp format on row_to_json

Consider tableA which has column row_added_dttm of type timestamp without timezone. The actual value is 2017-08-31 18:34:42.813175.

After I executed the below query, it results in a timestamp with timezone like {"crt_dttm":"2017-08-31T18:34:42.813175"}.

select row_to_json(t) from (select row_added_dttm from tableA limit 1) as t;

But the format which I require is something like 2017-08-31T18:34:42.813Z. I am not sure how to generate that, please help. Using row_to_json is required.

Solution

  • There is no way to influence the format used by row_to_json.

    You could define a view on your table and use

    to_char(row_added_dttm, 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"')

    to format the timestamp as a string.

    Then you can use row_to_json on that view to get your desired result.