Drizzle storing -1 hour in TIMESTAMP
Hey guys, im having an issue with timestamp column using Postgres database. When logging a date I get correct current time, however after storing it in database, there is one hour less... I have set my database to +1 time zone
this is how the column is defined:
expectedTimeDone: timestamp('expected_time_done').notNull(),
I have tried setting the timestamp option withTimezone: true, but this doesnt solve it.
this is how i set the column data:
const expectedDone = new Date();
// Expected done: Thu Jan 18 2024 02:46:07 GMT+0100 (Central European Standard Time)
expectedDone.setMinutes(expectedDone.getMinutes() + newTask.duration);
// Expected done after adding minutes: Thu Jan 18 2024 02:46:07 GMT+0100 (Central European Standard Time)
then
db.insert(table).values({expectedTimeDone: expectedDone})
I can just add one hour to the expectedDone, but no. How do I solve this please?
using node-postgres11 Replies
There is an issue with the postgres driver
Can you install the beta version of drizzle a see if that solves your problem?
Here, for context: https://github.com/drizzle-team/drizzle-orm/pull/1659
GitHub
[Pg] Fix: all datetime mappings by Angelelz · Pull Request #1659 · ...
This PR will close #806, close #971, close #1176, close #1185, close #1407 and close #1587.
Most are the same. The problem is postgres.js, it didn't have a clear way of replace the default pars...
Tried right now, still the same issue
{
"expectedTimeDone": "2024-01-18T02:30:40.725Z",
"createdAt": "2024-01-18T03:28:40.727Z",
"updatedAt": "2024-01-18T03:28:40.727Z"
}
Also tried removing the withTimeZone, still same
i see this is for postgres.js, im using node-postgresWhat is you timezone?
Europe/Prague, CET
I would try using dates as string and see what you get
You can manually do
new Date().toISOString()
to insert new datesIts very weird, I have been doing more testing. I have my TZ env variable set to Europe/Prague in node and in postgres as well, postgres saves the timestamps in correct timezone and node even logs the correct time zone when logging new Date(). However this one column which i set manually from node and isnt .defaultNow(), gets -1 hour when returning it in a http response json, but when logging it, i get the correct hour and time zone... I also tried chaning my TZ variables to Europe/London both in node and postgres and everything worked as it should.
Tried just now, still doesnt work. It saves the date in wrong time zone again
Do recieve back a string?
I save it using
new Date().toISOString(), this gets saved -1 hour in database, then i receive the same value as is saved in database as an ISO string, so YesInteresting, I would do the same but
withTimestamp
Remember, you need to run a migration or push if you make this changeDo you mean
withTimezone? I tried that just now and it actually finally works. Still tho I think there is some issue with the timezones because if I set my TZ env to Europe/London on node and postgres, the timezones are the same everywhere even with mode: 'date'