Postgres timestamp with time zone after updating to the 5.1.1 packages

[GeertVeenstra]

Hello,

After updating my project to the latest packages and creating a new migration, all the datetime columns in my PostGres database are converted to "timestamp with time zone" (previously they were "timestamp without time zone").

I assume this is because the new npgsql 6.0 is used, but I see no mentioning in the release notes about this breaking change?

Geert Veenstra

[maliming]

See
https://www.npgsql.org/doc/types/datetime.html#timestamps-and-timezones
#11230 (comment)
#11284

[GeertVeenstra]

I am aware of that, thanks.

When I add

AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);

to the EntityFrameworkCoreModule PreconfigureServices of my project then I have no problem (the column changes do not appear in the Migration).

I see for 5.1.1 this was moved to the AbpDbContext.
Any chance this is causing the issue now?

[maliming]

How do you add new migrations? dotnet ef command?

[GeertVeenstra]

Within visual studio Package Manager console --> Add-Migration "Update_To_Abp_5.1.1"

[maliming]

Can you try to use dotnet ef command?

[xxvolvo]

AbpDbContext
@maliming
the same problem both withint dotnet ef and visual studio Package Manager console

[sanghel-payoff]

Can definitely confirm this issue, while not even using 5.1.1.

I've started a new solution with AbpSuite 5.0.1 a week ago.
I've subsequently added the initial migration from PackageManagerConsole. This defined all DateTime entity props as timestamp with timezone, which is my desired config.

Without having made any change whatsoever to the sources, and no Nuget package upgrade, running an Add-Migration today yields a migration that changes the pgsql data type of all DateTime props from timestamp with timezone to timestamp without timezone.

I have no idea what is causing this change....
I've tried setting

Configure<AbpClockOptions>(options =>
            {
                options.Kind = DateTimeKind.Utc;
            });

in ConfigureServices() of all these modules:

• *EntityFrameworkCoreModule
• *ApplicationModule
• *DomainModule

but the undesired migration is still being generated.

Where is the behaviour for DateTime prop configured during entity configuration, in particular for Npgsql?

This is a really blocking, correctly storing datetimes with timezone information is crucial for a Saas app...

[sanghel-payoff]

@xxvolvo I DO NOT require the legacy behaviour, but the new Npgsql 6+ one.

Starting with 6.0, Npgsql maps UTC DateTime to timestamp with time zone, and Local/Unspecified DateTime to timestamp without time zone; trying to send a non-UTC DateTime as timestamptz will throw an exception, etc.

It is obviously the path forward with Npgsql, and also a better default behaviour.

So the question is, how do I instruct abp to correctly configure DateTime props to timestamp with timezone, in all referenced framework modules, and in the abp suite generated modules ?

[sanghel-payoff]

I'm not sure if Volosoft is giving the adequate attention to this issue.

Correctly writing / reading DateTime props via EFCore has some non trivial issues, see https://stackoverflow.com/questions/4648540/entity-framework-datetime-and-utc

I'm unsure if / how these aspects are correctly handled by the abpframework.

Tightly related to this, I would consider prioritizing to maximum level the usage of NodaTime in the base framework, as requested by #263

I cannot wholeheartedly continue using ABP Commercial until solid handling of timezone-rich DateTime information is implemented and documented...

[VAllens]

Add two lines of code to the Main method:

 AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);
 AppContext.SetSwitch("Npgsql.DisableDateTimeInfinityConversions", true);

The problem is gone.