PostgreSQL outputs different TimeStamp format than Javascript TimeStamp format

Question

I'm having trouble binding the results from SQL to my Model in Javascript /Typescript.

In my model, I have a property of created_at with types of Date.

When I use JSON functions in the Postgres SQL statement to avoid duplicate parent rows for a relationship, I will get a different format for the timestamp.

Here is a simple example

SELECT
   a.*,
   (
      SELECT
         ROW_TO_JSON(profile) 
      FROM
         (
            SELECT
               *
            FROM
               profile p 
            WHERE
               p.account_id = a.id 
         )
         profile 
   )
   AS profile 
FROM
   account a 
WHERE
   a.id = 16

And here are the results in JSON

{
   "id":16,
   "email":"[email protected]",
   "password":"$password",
   "role_id":0,
   "created_at":"2020-04-01T22:03:44.324Z",
   "profile":{
      "id":8,
      "username":"firmanjml",
      "gender":0,
      "bio":null,
      "avatar":"www.firmanjml.me/test.jpg",
      "account_id":16,
      "created_at":"2020-04-02T06:03:44.32498"
   }
}

I noticed that the parent row which is from the account table has the Z at the end of created_at whereas the child table that is converted to JSON has a different timestamp format.

Is there a way that I could make all the timestamp be in Javascript format?

Query to create schema and insert data

CREATE TABLE "account"(
    id SERIAL primary key,
    email varchar(50) not null,
    password varchar(50) not null,
    role_id int2 default 0 not null, 
    created_at timestamp default now() not null
);

CREATE TABLE "profile"(
    id SERIAL primary key,
    username varchar(50) not null,
    gender int2 not null,
    bio varchar(50),
    avatar varchar(50),
    account_id integer not null REFERENCES account (id),
    created_at timestamp default now() not null
);

INSERT INTO "account" (email,"password","role_id",created_at) VALUES 
('[email protected]','$password',0,'2020-04-02 06:03:44.324');

INSERT INTO "profile" (username,gender,bio,avatar,account_id,created_at) VALUES 
('fimrnajml',0,NULL,'www.firmanjml.me/test.jpg',1,'2020-04-02 06:03:44.324');

Answer 1

Use the TO_CHAR() function to format the timestamp in your SQL, like https://www.postgresqltutorial.com/postgresql-to_char/

A format of 'YYYY-MM-DD"T"HH24:MI:SS.US"Z"' should do it. This assumes all your timestamps are in UTC (the way the pros do it :-)

Your SQL then looks like:

SELECT
   a.*,
   (
      SELECT
         ROW_TO_JSON(profile) 
      FROM
         (
            SELECT
               username,gender,bio,avatar,account_id,to_char(created_at, 'YYYY-MM-DD"T"HH24:MI:SS.US"Z"') created_at
            FROM
               profile p 
            WHERE
               p.account_id = a.id 
         )
         profile 
   )
   AS profile 
FROM
   account a
WHERE
   a.id = 16

try it here