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 — user330315
– user330315, Commented Jul 15, 2020 at 20:32

Sting Tan · Accepted Answer · 2021-05-10 17:46:56Z

10

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

answered May 10, 2021 at 17:46

Sting Tan

1011 silver badge4 bronze badges

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

1 Comment

IlnurIbat Jan 25 at 20:18

better to use named oid instead of number. 1082 -> pg.types.builtins.DATE

Adrian Klaver · Accepted Answer · 2020-07-15 22:26:18Z

5

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)

answered Jul 15, 2020 at 22:26

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

Comments

Tyler2P · Accepted Answer · 2023-11-11 22:16:22Z

1

The reason is that when you query the date column, it's exactly that; a DATE.

Not a string, so we need to convert it to string first
Same as using .toString() with JavaScript.

Okay, back to your question: Try:

SELECT DATE(date_added)::text AS date_added from [table_name]

edited Nov 11, 2023 at 22:16

Tyler2P

2,37030 gold badges26 silver badges34 bronze badges

answered Nov 10, 2023 at 14:48

Katleho-codes

436 bronze badges

Collectives™ on Stack Overflow

Date type displaying with timezone on node-postgres module

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related