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');

The JS timestamp is wrong by my definition. 'Z' is a timezone, and unless you're using it EVERYWHERE then you should mark it as such. — Richard
– Richard, Commented Apr 2, 2020 at 10:02
Hello, thanks for your response. My typescript model can only bind the rows data if the timestamp comes with the Z, the rest of the created_at will give me an error. I want all the timestamp to have the 'Z' symbol. Without the Z, it will detect it as invalid DateTime format. — Firman Jamal
– Firman Jamal, Commented Apr 2, 2020 at 10:15

Richard · Accepted Answer · 2020-04-08 23:59:20Z

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

edited Apr 8, 2020 at 23:59

answered Apr 2, 2020 at 10:03

Richard

6133 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Firman Jamal Over a year ago

'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"' Fixed it. Thank you!

mythicalcoder Over a year ago

Yes .MS"Z" fixed the issue. Why converting to JSON strips away "Z" at the end? Postgres by default sends it out with "Z" for regular UTC timestamp.

Richard Over a year ago

The "Z" sets the time zone of the timestamp explicitly to UTC. The default behavior is not to set a time zone at all, and PostgreSQL assumed the system time zone is the correct one (I think, it's been awhile). See en.wikipedia.org/wiki/ISO_8601#Time_zone_designators

Collectives™ on Stack Overflow

PostgreSQL outputs different TimeStamp format than Javascript TimeStamp format

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related