[BUG]: Postgres insert into timestamp with time zone removes milliseconds

[hapawillow]

What version of drizzle-orm are you using?

0.27.2

What version of drizzle-kit are you using?

0.19.12

Describe the Bug

When I insert a timestamp the milliseconds are removed.

• Column definition: lastSignInAt: timestamp('last_sign_in_at', { withTimezone: true }).notNull()
• Data I'm inserting: const now = new Date();
• console.log(now) displays 2023-08-15T21:36:45.531Z
• Sample insert statement: const data = await db.insert(accounts).values({ lastSignInAt: now }).returning({ createdAt: accounts.createdAt });
• console.log(data.createdAt) displays 2023-08-15T21:36:45.000Z

Might be related to issued #877

Expected behavior

Timestamp should be stored with milliseconds
In the example above, data.created should be 2023-08-15T21:36:45.531Z

Environment & setup

No response

[nicobao]

For me it inserts the milliseconds, but then selects by truncating them. So when I do a comparison in a where clause using data previously selected, I get unexpected results :/.
I use timestamp without timezone.

[Angelelz]

This will be closed by #1659

[Angelelz]

You'll just need to use the mode: 'string' because the javascript Date object doen't support microsecond precision.

[nicobao]

You'll just need to use the mode: 'string' because the javascript Date object doen't support microsecond precision.

Javascript Date supports milliseconds:

> new Date()
2024-01-06T20:52:05.354Z
> new Date().getMilliseconds()
862

You said microseconds. I don't care about those but I'd like to keep the milliseconds.

Can I do this to keep the "date" mode:

timestamp("updated_at", {
        mode: "date",
        precision: 3, // 3 for milliseconds
  })

According to the Postgres doc, Postgres default to the precision of the literal value, so it should have been 3 by default, no?

[WildEgo]

You'll just need to use the mode: 'string' because the javascript Date object doen't support microsecond precision.

Javascript Date supports milliseconds:

> new Date()
2024-01-06T20:52:05.354Z
> new Date().getMilliseconds()
862

You said microseconds. I don't care about those but I'd like to keep the milliseconds.

Can I do this to keep the "date" mode:

timestamp("updated_at", {
        mode: "date",
        precision: 3, // 3 for milliseconds
  })

According to the Postgres doc, Postgres default to the precision of the literal value, so it should have been 3 by default, no?

Currently facing the same issue on a returning query, when I Select it normally it seems to work perfectly but for some reason the returning just removes the precision

[Angelelz]

You'll just need to use the mode: 'string' because the javascript Date object doen't support microsecond precision.

Javascript Date supports milliseconds:

> new Date()
2024-01-06T20:52:05.354Z
> new Date().getMilliseconds()
862

You said microseconds. I don't care about those but I'd like to keep the milliseconds.
Can I do this to keep the "date" mode:

timestamp("updated_at", {
        mode: "date",
        precision: 3, // 3 for milliseconds
  })

According to the Postgres doc, Postgres default to the precision of the literal value, so it should have been 3 by default, no?

Currently facing the same issue on a returning query, when I Select it normally it seems to work perfectly but for some reason the returning just removes the precision

They just published a fix for this in the beta tag. Can you check if that fixes the issue?

[WildEgo]

You'll just need to use the mode: 'string' because the javascript Date object doen't support microsecond precision.

Javascript Date supports milliseconds:

> new Date()
2024-01-06T20:52:05.354Z
> new Date().getMilliseconds()
862

You said microseconds. I don't care about those but I'd like to keep the milliseconds.
Can I do this to keep the "date" mode:

timestamp("updated_at", {
        mode: "date",
        precision: 3, // 3 for milliseconds
  })

According to the Postgres doc, Postgres default to the precision of the literal value, so it should have been 3 by default, no?

Currently facing the same issue on a returning query, when I Select it normally it seems to work perfectly but for some reason the returning just removes the precision

They just published a fix for this in the beta tag. Can you check if that fixes the issue?

I'm currently at the gym, I beg give it a look in a couple of hours, just let me know the version and I'll upgrade

[WildEgo]

@Angelelz Hey mate just to let u know [email protected] corrects the issue indeed

[mdurling]

This is still a problem for inserts that use .returning() ... It's not a problem for selects. Also, I only experience the problem with the postgres driver. neon-serverless works correctly OTOH.

    "@neondatabase/serverless": "0.8.1",
    "drizzle-orm": "0.29.4",
    "postgres": "3.4.3",

[AndriiSherman]

Should be fixed in drizzle-orm@beta. I would appreciate some feedback to confirm whether this issue has been resolved in this tag.

I plan to release it in version 0.30.0 tomorrow or within the next few days; I simply aim to address this substantial set of issues we're encountering. I'll be duplicating this message across all similar issues we're facing.

[WildEgo]

Should be fixed in drizzle-orm@beta. I would appreciate some feedback to confirm whether this issue has been resolved in this tag.

I plan to release it in version 0.30.0 tomorrow or within the next few days; I simply aim to address this substantial set of issues we're encountering. I'll be duplicating this message across all similar issues we're facing.

I've previously replied to this and stated that it was working perfectly for me and it not being on stable was breaking my environment

[mdurling]

The issue I reported with .returning() is resolved in drizzle-orm 0.29.5-e0ead65.

[AndriiSherman]

Fixed in [email protected]

Please check release notes for more info