problems with timestamp

[MarcRohrer]

Hi,

I decided to reimplement all the datebase stuff, as Postgres.js
seems to be a way better solution.

Unfortunately I have run into a problem with timestamps.
I use them as part of the primary key and also in foreign keys.

I insert into a master table returning the default timestamp
column value generated with now() This gives:

Thu Jul 27 2023 14:29:51 GMT+0200 (Mitteleuropäische Sommerzeit)

This has to be used as part of the foreign key in other tables.
Everything shorter the a second vanished.

selecting from Postgres.js gives:

2023-07-27T12:09:58.385 Z

selecting within pgAdmin gives:

2023-07-27 14:09:58.385007+02

Of course foreign keys based on that value do not work at all.
Application logic requires two separate transactions.
Postgres.js and pgAdmin statements were identical.
All timestamps are with timezone.

Am I doing something wrong here?

Best wishes!

Marc

[porsager]

Hi Marc

Sounds exiting 😊

Timestamps are by default cast to js Date which isn't as high precision as postgres. You'll want to either explicitly cast to ::text in each query, or add a custom type that disables default Date cast. Like this:

const sql = postgres({
  types: {
    date: {
      to: 1184,
      from: [1082, 1114, 1184],
      serialize: x => x,
      parse: x => x
    }
  }
})

[MarcRohrer]

Hi Rasmus,

thanx for your reply!

Great! Works until the string is converted back to a Date for insertion.
I have multiple rows in one array which I want to insert like this:

await sql`insert into the_table ${sql(multiple_rows_in_array)};`

When I create the array like this:

const multiple_rows_in_array = [{id: id_1, ts: sql`TO_TIMESTAMP(the_timestamp)`}]

I get a syntax errror from Postgres before I even have a chance to insert the data.
The timestamp actually is the same for all inserted rows.

By the way, is there a possibility to also show the values of an insert statement?
.statement() does not output the actual values...
If something is wrong (like inserting a value in the wrong column),
this would be extremely handy.

Best wishes!

Marc

[mattbishop]

I had a similar problem recently and used BigInt to hold the unix epoch value (accurate to microseconds) in javascript and in the SQL statements and then converted to timestamp in the DB. Here is a blog post describing the technique.

https://bigdatansql.com/2020/08/16/postgresql-bigint-value-conversion-to-utc-and-local-times/