How to save JS Date.now() in PostgreSQL?

Question

I tried to use PostgreSQL timestamp datatype, but it throws an error

ERROR:  date/time field value out of range: "1489849402536"

The schema

create table times (
  time   timestamp    not null,
);

JS code

`insert into times(time) values (${Date.now()})`

P.S. another option is to use bigint but it seems like an overkill.

Try casting to timestamp in your query. IIRC it would be `${Date.now()}::tstamp` — Jared Smith, Mar 18 '17 at 15:18

Udi · Accepted Answer · 2017-03-19T11:06:51.313

62

`insert into times (time) values (to_timestamp(${Date.now()} / 1000.0))`

edited Mar 19 '17 at 11:06

answered Mar 19 '17 at 09:12

Udi

12

By way of explanation for this answer, JavaScript `Date.now()` returns the number of _milliseconds_ since the Unix Epoch (1 Jan 1970). PostgreSQL `to_timestamp(…)` converts a single argument, interpreted as the number of _seconds_ since the Unix Epoch into a PosgtreSQL timestamp. At some point, the JavaScript value needs to be divided by 1000. You could also write `to_timestamp(${Date.now()/1000})`. – Manngo Mar 19 '17 at 09:36
Thanks didn't knew that PostgreSQL uses seconds instead of milliseconds, so sadly there will be a data loss... – Alex Craft Mar 19 '17 at 10:49
2

To keep milliseconds, use `/ 1000.0` instead. I have fixed my answer above. – Udi Mar 19 '17 at 11:07
3

Why is the ${ } syntax needed? – Edward Oct 04 '17 at 17:50
It is string injection. You can write 'INSERT INTO times (time) VALUES (to_timestamp(' + Date.now() /1000.0 + '))' too. @Edward – Capan Oct 08 '19 at 15:25
Thanks Udi and Manngo. I been figuring out this for hours. – Rafael Parungao Feb 10 '21 at 11:04

Anton Kornus · Answer 2 · 2020-05-21T11:51:46.433

28

Or you can do something similar to this:

const time = new Date().toISOString();

In your query builder:

...
.update()
.set({column_name: time})

edited May 21 '20 at 11:51

answered May 20 '20 at 12:09

Anton Kornus

As an info `toString()` give time like this `Sat Mar 20 2021 22:02:18 GMT+0200 (Eastern European Standard Time)` – mercury Mar 20 '21 at 20:04
1

for me it gives `2022-06-15T12:51:35.138Z` which is also not compatible with Postgres – agoldev Jun 16 '22 at 12:52
this gives the time in GMT time zone – cikatomo Nov 06 '22 at 12:16

score 3 · Answer 3 · answered Mar 23 '20 at 14:29

3

Just in case you are using Sequelize, you can use: sequelize.literal('CURRENT_TIMESTAMP'). Example:

await PurchaseModel.update( {purchase_date : sequelize.literal('CURRENT_TIMESTAMP') }, { where: {id: purchaseId} } );

answered Mar 23 '20 at 14:29

3 Answers3