Writing DateTime values to a Date field in Postrgresql it is storing the previous date (Npgsql 6.0.4)

[PaulDMendoza]

When I try to store a date to a date column in Postgresql the date that gets stored is always the previous date.

For example, the unit test below tries to store the date 2021-01-01 to the "datefield" and "failuredates" array but it fails with the below error message.

[TestMethod]
        public async Task NpgsqlDateWritingTest2()
        {
            using var sp = await TestHelpers.GetServiceProviderAsync(new TestOptions(Dataset.V1));
            var connection = sp.GetRequiredService<ICenterDBConnection>();
            var context = CenterDBContext.CreateFromConnection(connection);

            var d = new DateTime(2021, 1, 1, 0, 0, 0, DateTimeKind.Utc);

            var entity = new webhook
            {
                action = WebhookActionTypes.email_new_distinct,
                created = d,
                enterpriseid = TestData.EnterpriseID,
                userid = TestData.PrimaryUser.UserID,
                webhookurl = "https://webhook.site/#!/FAKE",
                webhookid = Guid.NewGuid(),
                failuredates = new[] {d},
                datefield = d
            };
            await connection.Connection.ExecuteAsync(@"insert into webhook (action, created, enterpriseid, userid, webhookurl, webhookid, failuredates, datefield)
VALUES (:action, :created, :enterpriseid, :userid, :webhookurl, :webhookid, :failuredates, :datefield);
", entity);

            var context2 = CenterDBContext.CreateFromConnection(connection);
            var webhook = context2.webhooks.First(w => w.webhookid == entity.webhookid);
            Assert.AreEqual(d, webhook.datefield, "date should be equal");
            Assert.AreEqual(d, webhook.failuredates.First(), "failuredates should be equal");
        }

Error message:

Assert.AreEqual failed. Expected:<1/1/2021 12:00:00 AM>. Actual:<12/31/2020 12:00:00 AM>. date should be equal

I can see in the database the value is the bad value of 2020-12-31 instead of 2021-01-01.

You can see in my unit test that my assert statement for the "created" field is fine.

Environment Details

Npgsql 6.0.4 .NET 6 PostgreSQL 13.6

[roji]

@PaulDMendoza there's lots missing from the above code, e.g. what is the actual PG column type, the EF Core model you're using (it seems like you're mixing both EF Core and Dapper?).

When submitting issues, please include a runnable, minimal and self-contained code sample that reproduces the problem. For example, the code below uses raw ADO.NET (no Dapper or EF Core to isolate the driver):

await using var conn = new NpgsqlConnection("Host=localhost;Username=test;Password=test");
await conn.OpenAsync();

using var command = new NpgsqlCommand(@"
DROP TABLE IF EXISTS data;
CREATE TABLE data (date date)", conn);
await command.ExecuteNonQueryAsync();

// INSERT
var d = new DateTime(2021, 1, 1, 0, 0, 0, DateTimeKind.Utc);
command.CommandText = "INSERT INTO data (date) VALUES ($1)";
command.Parameters.Add(new() { Value = d });
await command.ExecuteNonQueryAsync();

// SELECT back
command.CommandText = "SELECT * FROM data";
command.Parameters.Clear();
Console.WriteLine(await command.ExecuteScalarAsync());

The result is as expected: 1/1/2021 12:00:00 AM

[PaulDMendoza]

To add even more clarification.

I just restarted my machine after changing the time zone to UTC and I'm still getting the error. I added a Console.WriteLine for the current time so I could show the time and offset that .NET is detecting.

My Timezone Configuration

[PaulDMendoza]

I'm closing this and making an issue in the Npgsql repo instad since this is for EFCore.

[PaulDMendoza]

https://github.com/npgsql/npgsql/issues/4471