Different result when querying PostgreSQL through CLI vs through Node.js server

Question

I have a simple node.js server that accepts requests & queries a database. When I try to query all balances using the following function


const Pool = require('pg').Pool;
const pool = new Pool({
  user: 'myUsername',
  host: 'localhost',
  database: 'myDatabase',
  password: 'myPassword',
  port: 5432,
});

const getBalances = (request, response) => {
    pool.query('SELECT * FROM balances', (error, results) => {
      if (error) {
        throw error
      }
      response.status(200).json(results.rows)
    })
};

I get the following result (ignore the balances, the relevant part dates)

[{"balance":400,"last_received":"2020-06-20T22:00:00.000Z"},
{"balance":200,"last_received":"2020-06-20T22:00:00.000Z"}, 
{"balance":200,"last_received":"2020-06-20T22:00:00.000Z"}, 
{"balance":400,"last_received":"2020-06-20T22:00:00.000Z"}]

This is odd, as the last_received should point to today's date. When I check this using the PostgreSQL CLI, I get the following result using the same query as in the node.js code:

balance | last_received 
--------+--------------
 400    | 2020-06-21
 200    | 2020-06-21
 200    | 2020-06-21
 400    | 2020-06-21

The Date is not the same, and it doesn't return the time as when I query using node.js either. The date datatype I used for creating the table is the standard DATE datatype from PostgreSQL. Why is this and how do I resolve this?

What I've tried: I've tried re-querying and restarting both multiple times to be sure it's not some sort of timing error, but each time the same result and I've queried show timezone and gotten the same result on both ends.

Additionally, when querying select now() I get the correct and same date on both views, yet when I query select CURRENT_DATE they are different by a day, indicating the problem lies somewhere with the CURRENT_DATE keyword.

Update: I've noticed this is not just a one-time thing, every new date I insert over the CLI comes out as one day before through the server. — user8352734
– user8352734, Commented Jun 21, 2020 at 13:14

Felipe Zavan · Accepted Answer · 2020-06-21 14:07:47Z

1

node-postgres converts Date types to Datetime in the local timezone, so it's converting to your server/node timezone.

So, either adjust the dates after fetching them, adjust your server/node timezone, or override the type parser.

answered Jun 21, 2020 at 14:07

Felipe Zavan

1,8531 gold badge20 silver badges36 bronze badges

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

2 Comments

Felipe Zavan Over a year ago

As a personal opinion, I'd like to say that I think the library is wrong in doing this and should simply return a Date type.

user8352734 Over a year ago

I definitely agree with you - Thank you so much for your answer!

Collectives™ on Stack Overflow

Different result when querying PostgreSQL through CLI vs through Node.js server

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related