typeorm: Time zones for timestamp columns are incorrectly fetched and persisted in PostgreSQL

I’ve looked into this problem and can also reproduce the problem seen by @slavafomin.

The problem is this:

TypeORM adjusts values of ‘date’ type (Date, ‘timestamp’, ‘timestamp with timezone’ etc) to UTC before persisting them to the database [PostgresDriver.ts#293]. It also adjusts back to localtime when hydrating values from the database [PostgresDriver.ts#323]. So as long as we stay in TypeORM-land then everything is fine.

However, this client-side conversion is contrary to how timestamp fields are expected to be used in Postgres and can cause problems when using other libraries or admin tools that are expecting you to follow the Postgres way.

Generally, timestamp (and timestamp without timezone) fields are used to store timestamps in localtime and no conversion to/from UTC should be performed by the client or server.

Conversely, timestamp with timezone are ALWAYS stored in UTC and Postgres will internally do a conversion to UTC on the supplied value (using it’s timezone if specified or defaulting to the server timezone if not).

Because TypeORM is attempting to replicate behaviour that Postgres already does for timestamp with timezone fields then you can get into a situation where values look like they have been corrected for DST twice. e.g. For me, in London on BST, I store a time of 1:33pm and it end up in the database as 11:33am even though BST has only 1 hour difference from UTC.

I suggest that the TypeORM Postgres Driver should never try to adjust for UTC but should defer to Postgres. The default field type for dates should be changed to ‘timestamp with timezone’. In the case where you really DO want to store local time, then you can by overriding the default column type by using timestamp without time zone

I have forked the project and am preparing a PR for this.

@robinsummerhill while it is true that change here will cause problems to existing users, I would say that keeping faulty implementation just because it had bug some time ago is even worse. And the problem is going to grow over time.

The v0.x is there for a reason, and I would suggest that with the upcoming 0.2.0 the changes should be done, with the migration stuff documented.

OR, at least do it in 2 steps: in 0.2.0 introduce a way to fix, with some warns/deprecations about upcoming default behaviour changes, and in 0.3.0 make it default.

Thinking about this further, TypeORM should not be manipulating times for time zone adjustment at all for any database. A Javascript Date object should follow a straight path through TypeORM from the client to the database driver with no modification. A Javascript Date represents a unique instance in time internally as milliseconds since 1970-01-01T00:00:00Z and timezones do not come into it. It is only when you want to convert a Date to/from a string to show a human that timezones become an issue. Showing dates to users is the domain of the application and TypeORM should not be getting involved.

It also leads to subtle bugs. Consider this case: I have an application server in Boston, US (UTC-5) and another one in London(UTC+0) talking to a database in London(UTC+0). A user in Boston creates a new order in the system at 7:00am local time. This is 07:00-5 (equivalent to 12:00+0). TypeORM adds five hours to this and saves it into the database (12:00-5 or 17:00+0). For the user in Boston, when they want to retrieve this record, the Boston app server subtracts 5 hours and so everything works ok. But for a user in London, the London app server will adjust by zero hours and so will return a time that is incorrect.

Timezones are hard.

Got temporarily around this by setting process.env.TZ to UTC It is suggested to set this environment variable before launching node process though.

@neumartin I cannot answer your questions sadly, but I suggest you to open a new issue instead of commenting on years old questions.

@pleerock Any updates on this or any help wanted with a fix? This is a critical issue for us 😕

Encouraging usage of bad implementation is not so good so I would do the opposite. Fix the behavior and add an option to enable the old one.