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 https://github.com/brianc/node-pg-types/pull/119#issuecomment-654571908 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)
})