timestamp,insert and query display are different

[w345731923]

To view:https://node-postgres.com/features/types ->date / timestamp / timestamptz

create table:

CREATE TEMP TABLE dates(
date_col DATE,
timestamp_col TIMESTAMP,
timestamptz_col TIMESTAMPTZ,
);

Insert first,then query.

E:\nodejs_space\hello>node time.js
process.env.TZ = Asia/Shanghai
now = Tue Oct 27 2020 10:08:28 GMT+0800 (GMT+08:00)
prepareValue = 2020-10-27T02:08:28.816Z
dateToString = 2020-10-27T10:08:28.816+08:00
[
{
date_col: 2020-10-26T16:00:00.000Z,
timestamp_col: 2020-10-27T02:08:28.816Z,
timestamptz_col: 2020-10-27T02:08:28.816Z
}
]

output psql: (insert time type was right)

highgo=# select * from public.dates;
date_col | timestamp_col | timestamptz_col
------------+-------------------------+----------------------------
2020-10-27 | 2020-10-27 10:08:28.816 | 2020-10-27 10:08:28.816+08
(1 row)

Why is the value of the time type different?

psql:---2020-10-27 10:08:28.816
timestamp_col:--- 2020-10-27T02:08:28.816Z

[charmander]

Date parameters are serialized as local time by default in current pg, with the timezone dropped when the parameter is interpreted as a timestamp. This is wrong (there’s no guarantee that local time can support all valid timestamp values, and it often doesn’t), but still exists for backwards compatibility.

See brianc/node-pg-types#119 (comment) for links to relevant discussion.

As a workaround for now, there’s pg.defaults.parseInputDatesAsUTC = true (undocumented) with a corresponding custom timestamp parser.

const parseTimestampTz = require('postgres-date')

types.setTypeParser(types.builtins.TIMESTAMP, (value) => {
  let utc = value.endsWith(' BC')
    ? value.slice(0, -3) + 'Z BC'
    : value + 'Z'

  return parseTimestampTz(utc)
})