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