[BUG]: update timestamp field (using AWS Data API)

[benjamine]

trafficstars

What version of drizzle-orm are you using?

0.27.2

What version of drizzle-kit are you using?

0.19.5

Describe the Bug

I'm trying drizzle-orm (with sst) and aurora pg thru aws data api, when trying to insert (or update) a Date value in a column (timestamp with timezone), I'm getting this error. I wonder if the problem is the way Date is being serialized (looks like something is using a toString instead of using ISO date string?)

this shows the response coming from aws-data-api

{
    "errorType": "BadRequestException",
    "errorMessage": "Cannot parse date or time \"Sat, 15 Jul 2023 05:58:12 GMT\"",
    "name": "BadRequestException",
    "$fault": "client",
    "$metadata": {
        "httpStatusCode": 400,
        "requestId": "ef9415d4.......64414",
        "attempts": 1,
        "totalRetryDelay": 0
    },
    "stack": [
        "BadRequestException: Cannot parse date or time \"Sat, 15 Jul 2023 05:58:12 GMT\"",
        "    at de_BadRequestExceptionRes (file:///var/task/packages/functions/src/auth.mjs:32546:21)",
        "    at de_ExecuteStatementCommandError (file:///var/task/packages/functions/src/auth.mjs:32452:19)",
        "    at processTicksAndRejections (node:internal/process/task_queues:96:5)",
        "    at async file:///var/task/packages/functions/src/auth.mjs:26732:20",
        "    at async file:///var/task/packages/functions/src/auth.mjs:25599:18",
        "    at async file:///var/task/packages/functions/src/auth.mjs:27222:38",
        "    at async file:///var/task/packages/functions/src/auth.mjs:24576:22",
        "    at async AwsDataApiPreparedQuery.values (file:///var/task/packages/functions/src/auth.mjs:33520:20)",
        "    at async AwsDataApiPreparedQuery.execute (file:///var/task/packages/functions/src/auth.mjs:33499:18)",
        "    at async upsertUser (file:///var/task/packages/functions/src/auth.mjs:38670:18)"
    ]
}

Expected behavior

row is updated with the provided Date object value

Environment & setup

AWS aurora postgres, AWS Data API

[kyen99]

I created a PR that I think fixes this. I'd love some feedback from anyone on how this could be better... https://github.com/drizzle-team/drizzle-orm/pull/1316

[philwinder]

@kyen99 FYI I also found a workaround, this seems to work for me.

With a table defined as:

import { pgTable, timestamp } from "drizzle-orm/pg-core";

pgTable('some_table', {
	timestamp: timestamp("timestamp").defaultNow(),
...

Then you can set that field with:

timestamp: sql`${new Date().toISOString()}::timestamp`,

Note the ::timestamp cast.

[jakeleventhal]

This is most likely closed by #1659

[kyen99]

Thanks @jakeleventhal, I see a fix went out in 0.29.2. This issue can be closed.

[AndriiSherman]

Was fixed in 0.29.2, so closing this one

[danclaroni]

this was working for me for a while, but now this same issue appears to exist with between query filters when the value is a date object and when inserting again:

	return await db.query.table.findMany({
		where: (table, { eq, between }) =>
			and(
				eq(table.fkId, fkId),
				between(table.time, startDate, endDate),
			),
	});

export const upsert = async (itemToUpsert: NewItem[]) => {
	const upserted = await db
		.insert(table)
		.values(itemToUpsert)
		.returning();

	return upserted;
};

i'm using a pg table with the following column: time: timestamp('time', { mode: 'date', withTimezone: true }).notNull(),

[danclaroni]

The problem appears to be in drizzle-orm/pg-core/columns/timestamp.js. The offending code is:

  mapToDriverValue = (value) => {
    return this.withTimezone ? value.toUTCString() : value.toISOString();
  };

the toUTCString() doesnt work for the data api, so I am going to make a patch where I just always convert it to an ISO string. I'm assuming this has other implications for other drivers, but I don't know enough about that - seems like this could just always do ISO though?

[danclaroni]

FYI @AndriiSherman

[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.

[danclaroni]

@AndriiSherman I am now able to insert and select timestamptz with AWS Data API (v1), BUT i believe the conversion from DB date/time to typescript Date is incorrect. e.g.

SELECT start_time, EXTRACT(TIMEZONE FROM start_time) as timezone  FROM table;

yields (notice timezone is 0, which represents UTC or 0 offset):

start_time | timezone
2024-03-20 13:00:00 | 0

but in typescript it is serialized in my local timezone:

'Wed Mar 20 2024 13:00:00 GMT-0500 (Central Daylight Time)'

or

'2024-03-20T18:00:00.000Z'

[danclaroni]

Seems like you should be able to update your integrations tests to insert a specific time and then upon retrieval verify the same time that was inserted was retrieved (via toISOString or something similar)

[AndriiSherman]

@AndriiSherman I am now able to insert and select timestamptz with AWS Data API (v1), BUT i believe the conversion from DB date/time to typescript Date is incorrect. e.g.

SELECT start_time, EXTRACT(TIMEZONE FROM start_time) as timezone  FROM table;

yields (notice timezone is 0, which represents UTC or 0 offset):

start_time | timezone
2024-03-20 13:00:00 | 0

but in typescript it is serialized in my local timezone:

'Wed Mar 20 2024 13:00:00 GMT-0500 (Central Daylight Time)'

or

'2024-03-20T18:00:00.000Z'

do you have timestamp or timestamp with timezone?

[danclaroni]

Timestamp with time zone

[danclaroni]

@AndriiSherman just a heads up that i am still getting the issue on 0.30.1 with the timestamps being in different timezones. I am using timestamp with timezone in mode date. In the DB, it is in UTC, but I get it back in typescript in my local timezone with the same time. e.g. DB: 2024-03-10 11:49:00 TS: '2024-03-10T16:49:00.000Z' (Sun Mar 10 2024 11:49:00 GMT-0500 (Central Daylight Time))

[danclaroni]

@AndriiSherman see my last post in the general discord channel. I think I have a fix, but I'm not 100% sure why. Looks like some of my timestamps with timezones are coming back to drizzle without the '+' that has the timezone offset?

[dankochetov]

@danclaroni FYI, timestamp with timezone is only supported in Data API for Aurora Serverless V1, which won't be supported after December 31st, 2024. Currently, we prioritize supporting V2.

[patrick-geyer-21]

I can't figure out the fix from this thread. I'm on [email protected], Data API Aurora Serverless V1 postgres 13.12. Have tried date, timestamp, timestamp with date fields and all of them return error: BadRequestException: Cannot parse date or time "2024-05-06 00:00:00.000"

[danclaroni]

@patrick-geyer-21 here is my patch. Not sure it will work perfectly for you, but you can try it:

diff --git a/pg-core/columns/timestamp.js b/pg-core/columns/timestamp.js
index 58281ebfcf66073ce5f2d12d75d99eac98656669..f85f9e00f78e809b8cfc5d519e95a2111f66a7c0 100644
--- a/pg-core/columns/timestamp.js
+++ b/pg-core/columns/timestamp.js
@@ -27,7 +27,11 @@ class PgTimestamp extends PgColumn {
     return `timestamp${precision}${this.withTimezone ? " with time zone" : ""}`;
   }
   mapFromDriverValue = (value) => {
-    return new Date(this.withTimezone ? value : value + "+0000");
+    if (this.withTimezone && value.includes('+')){
+        return new Date(value);
+    }
+
+    return new Date(value + "+0000");
   };
   mapToDriverValue = (value) => {
     return value.toISOString();