Date type displaying with timezone on node-postgres module

Question

I have stored input data in date format in postgres database, but when I am showing the date in browser it's showing date with timezone and converting it from utc. For example I have stored the date in 2020-07-16 format. But when i am showing the date it becomes 2020-07-15T18:00:00.000Z. I have tried using select mydate::DATE from table to get only date but its still showing date with timezone. I am using node-postgres module in my node app. I suspect it's some configuration on node-postgres module? From their doc:

node-postgres converts DATE and TIMESTAMP columns into the local time of the node process set at process.env.TZ

Is their any way i can configure it to only parse date? If i query like this SELECT TO_CHAR(mydate :: DATE, 'yyyy-mm-dd') from table i get 2020-07-16 but thats lot of work just to get date

What data type is that column exactly? The "in the format" seems to indicate it's not a proper `date` column, but a `text` column — , Jul 15 '20 at 20:32

score 7 · Answer 1 · answered May 10 '21 at 17:46

You can make your own date and time type parser：

const pg = require('pg');
pg.types.setTypeParser(1114, function(stringValue) {
  return stringValue;  //1114 for time without timezone type
});

pg.types.setTypeParser(1082, function(stringValue) {
  return stringValue;  //1082 for date type
});

The type id can be found in the file: node_modules/pg-types/lib/textParsers.js

score 4 · Answer 2 · answered Jul 15 '20 at 22:26

It is spelled out here:

https://node-postgres.com/features/types

date / timestamp / timestamptz

console.log(result.rows)
// {
// date_col: 2017-05-29T05:00:00.000Z,
// timestamp_col: 2017-05-29T23:18:13.263Z,
// timestamptz_col: 2017-05-29T23:18:13.263Z
// }

bmc=# select * from dates;
  date_col  |      timestamp_col      |      timestamptz_col
------------+-------------------------+----------------------------
 2017-05-29 | 2017-05-29 18:18:13.263 | 2017-05-29 18:18:13.263-05
(1 row)

Date type displaying with timezone on node-postgres module

2 Answers2