CODEWITHSUNDEEP
javascriptdatabasepostgresql
Yaw Boakye
Yaw Boakye

Reputation: 10742

postgresql to_timestamp returns different date than represented by the timestamp

Here's a replication of the problem:

I get the timestamp of now via JavaScript as

var ts = +new Date // 1368971991090
console.log( new Date(1368971991090) ); // Sun May 19 2013 13:59:51 GMT+0000 (GMT)
Then in my Postgres console,

# select to_timestamp(1368971991090);
          to_timestamp          
--------------------------------
 45350-12-30 05:31:29.999872+00
(1 row)

A date way too much into the future.
The docs of JavaScript Date#getTime say it returns milliseconds starting from the epoch; same as the argument type Postgres' to_timestamp accepts. Java's Date#getTime returns same value as JS Date#getTime.

Upvotes: 2

Views: 1220

Answers (2)

Husman
Husman

Reputation: 6909

Don't know if this helps anyone, but I ran into the same issue recently. The solution was to convert the JS timestamp to seconds since the epoch, instead of milliseconds.

const millisecondsSinceEpoch = Date.now();
// output: 1631828038018
const secondsSinceEpoch = Math.round(Date.now() / 1000);
// output: 1631829225

In Postgres:

SELECT to_timestamp(1631828038018);
// output: 53680-07-27 14:06:57.999872

SELECT to_timestamp(1631829225);
// output: 2021-09-16 22:53:45+01

Upvotes: 0

Ihor Romanchenko
Ihor Romanchenko

Reputation: 28541

Quote from postgresql doc from your link:

it accepts a double precision argument and converts from Unix epoch (seconds since 1970-01-01 00:00:00+00) to timestamp with time zone.

Key -

seconds since

And in JS doc:

number of milliseconds since 1 January 1970 00:00:00 UTC

Upvotes: 7

Related Questions