How to insert node Date() into postgresql timestamp column?

Question

I want to update several records with the same time, and I can't figure out how to do it.

I've tried various combos with pg-promise and moment and node alone:

myTs = Date();
myTs = moment(new Date()).format("YYYY-MM-DD HH:mm:ss"); 

with sql:

UPDATE mytable set ts = $1;
UPDATE mytable set ts = $1::timestamp;

errors:

 time zone "gmt-0800" not recognized

Criticaster

Your error message is claiming that the column has a mismatched type: BIGINT instead of TIMESTAMP. Is that correct? If it is, you'll need to store your date in a numeric format and not as an ISO date string. – Cudlip

Use timestamptz as the database column type, and insert JavaScript type Date directly. It just will work, and correctly. – Hach

Answer 1

It is better to store timestamps in a database in UTC format without time zones.

Suppose there is a table (PostgreSQL):

create table test_time (utc_time timestamp);

To insert the current UTC timestamp:

const res = await db.query({
  text: "insert into test_time (utc_time) values ($1) returning *",
  values: [new Date(new Date().toISOString())],
});

Or you can insert the current Date with a time zone depending on your runtime:

const res = await db.query({
  text: "insert into test (date) values ($1) returning *",
  values: [new Date()],
});

Baud