fix(postgres): parse timestamp dates less than year 1000

[Robert-Schirmer]

Bug

Parsing a timestamp column in postgresql with a date less than year 1000 will create an invalid Date when using the forceUtcTimezone set to true.

Reproduction

Created test for reproduction, run without the fix to PostgresSqlConnection.ts.

Fix

Use the date parsing function from postgres-date that is used by default in pg for parsing dates but add the Z to force the utc timezone. new Date('0022-01-01 09:17:58.000Z') when parsing the timestamp column str creates an invalid date in js.

[codecov]

Codecov Report

All modified and coverable lines are covered by tests ✅

Comparison is base (6667656) 99.49% compared to head (d7d2bfa) 99.49%.

Additional details and impacted files

@@           Coverage Diff           @@
##              5.x    #5071   +/-   ##
=======================================
  Coverage   99.49%   99.49%           
=======================================
  Files         217      217           
  Lines       14732    14733    +1     
  Branches     3576     3576           
=======================================
+ Hits        14657    14658    +1     
  Misses         71       71           
  Partials        4        4           

☔ View full report in Codecov by Sentry.
📢 Have feedback on the report? Share it here.

[B4nan]

please target master branch, then once we merge, we can cherry pick to 5.x

[Robert-Schirmer]

please target master branch, then once we merge, we can cherry pick to 5.x

Thats what I originally wanted to do but there seems to be a refractor of the PostgreSqlConnection in master, it will require two different fixes. How do you want to do that?

[B4nan]

few notes about the test

[B4nan]

this is not making any query, as that entity is already in the identity map

Suggested change

	const res = await orm.em.find(TimestampTest, something.id);
	const res = await orm.em.fork().find(TimestampTest, something.id);

[B4nan]

no need to export anything

Suggested change

	export class TimestampTest {
	class TimestampTest {

[B4nan]

Suggested change

	await orm.schema.ensureDatabase();
	await orm.schema.execute('drop table if exists timestamp_test');
	await orm.schema.createSchema();
	await orm.schema.refreshDatabase();

[B4nan]

this should go into beforeAll hook, and the orm.close should go to afterAll, that way the connection will be closed even if the test fails (and the whole test suite wont get stuck, which can sometimes end with the CI running for 6 hours)

[B4nan]

Thats what I originally wanted to do but there seems to be a refractor of the PostgreSqlConnection in master, it will require two different fixes. How do you want to do that?

Then I'd like to first see a PR for master/v6, keep this one targeting v5. Once we have it fixed in v6, we can merge this one, but I don't want to fix things only here.

[B4nan]

Btw, just curious, what kind of dates are you storing that you've hit this? :]

[B4nan]

gave this a closer look, and its not just about postgres, neither it is about year 1000, just about some particular years close to 0. looks like its enough to add T into the datetime string instead of the space to fix this. fixing this in v6 is actually much easier than v5, as date parsing happens on a single place for all drivers. since nobody else asked about this, i am fine with merging this in for v5, and keeping the proper fix to all drivers in v6 only

edit: in the end its postgres problem only, as mysql wont even allow storing such a date, and sqlite handles this with timestampts, so this is not a problem there either