TimestampTZ issue datatable to postgres

[rajkamal0610]

While trying insert bulkmerge from datatable which has column type as datetime and inserting/updating to postgres table which has the field mapped with datatype timestamptz. After inserting we examine that the value of datetime is too different from the current timezone

[JonathanMagnan]

Hello @rajkamal0610 ,

Unfortunately, Postgres and timestamptz are very inconsistent (even themselves are not consistent from a major version to another). We did our best but depending on the version you are using, it might have some difference.

Could you provide a project with the issue? I will check with my developer if we could provide an option to let you choose how to handle those timestamptz value.

Best Regards,

Jon

[rajkamal0610]

Hi,

I have insert datetime value [via datatable] in the following table

Create table "TimeStampTimeZone" ( tz timestamptz, notz timestamp, value real )

[JonathanMagnan]

Hello @rajkamal0610 ,

Could you provide a project? It will give us information about which version of PostgreSQL you are using and some quick way to reproduce it.

As said, PostgreSQL handle timestamp difference from a version to another, from a binary copy to normal insert, with or without Entity Framework, and more... So a project will give us all information that we will need to try to find you the best solution.

[rajkamal0610]

Hi,

I attached sample project. two events are triggering

1.) old dates with value 2.) current date with value

Lastest z.bulkoperation.dll Npgsql 4.1.2 postgres 10/11 both binary and install version tries

use case :

1.) system time zone and postgres timezone same 2.) system time zone and postgres timezone different ex: system timezone UTC+5:30 but postgres US/Pacific [UTC-7]

we dont know which timezone is in the system. we need to run in any timezone without any issue

TestProjectPostgresTimeStampTZ.zip

[rajkamal0610]

Hi Jonathan,

Let me know any progress on the issue

Regards, Rajkamal S

[JonathanMagnan]

Hello @rajkamal0610 ,

The v3.0.13 has been released.

We added a new option to handle your case and to make sure we don't break other people case (such as those using Entity Framework with our library).

bulkOperation.UsePostgreSqlTimeZone  = true;

Let me know if that's what you were looking for or we missed something.

Best Regards,

Jon

[rajkamal0610]

Hi Jonathan,

Thanks for the update.

Will test with the new version and let me know you with the end results

Regards, Rajkamal S

[JonathanMagnan]

Awesome @rajkamal0610

I will be looking forward to hearing from you!

Jon

[JonathanMagnan]

Hello @rajkamal0610 ,

Did you get the time to try the version v3.0.13?

Don't hesitate to contact me for questions, issues or feedback!

Best regards,

Jon