Pomelo.entityframeworkcore.mysql: DateTime.MinValue has different meaning in the database.

[RheaAyase]

The issue

Subject says it all - see below steps to reproduce.

From user's point of view this seems to be a regression after update from 2.1.2 to 3.1.1 (probably related to the introduction of the AsyncEnumerable in EFCore and the changes where do which queries get executed.)

Steps to reproduce

1) Save a database entry with a datetime column, using DateTime.MinValue
2) Read it back.
3) Observe that it's read as 1970-01-01 00:00:01.000000 while DateTime.MinValue is in fact 0001-01-01 00:00:00.000000

Expected behavior

DateTime.MinValue should be correctly translated both ways. In other words, anything lower than 1970 will be stored as that, and translated back to proper MinValue in our dotnet application.

Further technical details

MySQL version: MariaDB 10.4.12
Operating system: linux-x64
Pomelo.EntityFrameworkCore.MySql version: 3.1.1
Microsoft.AspNetCore.App version: none (cli) .NET Core 3.1.3

[mguinness]

Can you confirm that the datatype of the column is datetime and not timestamp in DB?

The smallest datetime value is 1000-01-01 and timestamp value is 1970-01-01 in MySQL.

See DateTime Storage for more info regarding MySqlConnector which this provider uses.

[RheaAyase]

Yep:

> describe tablename;
+------------------+---------------------+------+-----+------------------+-------+
| Field            | Type                | Null | Key | Default          | Extra |
+------------------+---------------------+------+-----+------------------+-------+
| entry_id         | bigint(20) unsigned | NO   | PRI | NULL             |       |
| some_date_time   | datetime            | NO   |     | from_unixtime(0) |       |
+------------------+---------------------+------+-----+------------------+-------+

Which now leads me to perhaps different possible source of the problem, looking at the from_unixtime(0). The Entity class contains:

[Column("some_date_time")]
public DateTime SomeDateTime{ get; set; } = DateTime.MinValue;

... so perhaps the error happens at the initialization when adding a new entry where this may be ignored?

[mguinness]

What does the general query log show when inserting value, does it have any warning? Is there a reason why this field isn't nullable allowing a default of null?

[RheaAyase]

It looks like you're talking about the ID - it's irrelevant? It's always not null as a key?

[mguinness]

No, I meant the datetime field - do you need to set the value to 0001-01-01 to signify an unknown value?

[RheaAyase]

Pretty much, valid only > MinValue. Or at least that was the code before 3.1.1 - now I just added another boolean field cuz this became fishy.

[mguinness]

Ok, but I would've thought a null value would be more logical than introducing another boolean field.

[RheaAyase]

But DateTime in C#...

[mguinness]

For value types you can use Nullable struct to assign null values, i.e. Nullable<DateTime>.

[RheaAyase]

This code was written 5 years ago at least, ported from mono to netcore. I'm not here to discuss possible workaround, but the Actual Issue so let's please stop going off-topic.

Actually I don't even need to discuss the issue at hand. It's a bug somewhere, I've reported it, it doesn't affect me anymore, so hey I'm just gonna unsubscribe from this ticket for my own sanity.

[lauxjpn]

@RheaAyase The following sample code works fine and demonstrates, that DateTime.MinValue is correctly written and read from the database as 0001-01-01 00:00:00:

Sample code

```c#
using System;
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Pomelo.EntityFrameworkCore.MySql.Internal;

namespace IssueConsoleTemplate
{
public class IceCream
{
public int IceCreamId { get; set; }
public string Name { get; set; }
public DateTime BestServedBefore { get; set; }
}

public class Context : DbContext
{
    public DbSet<IceCream> IceCreams { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseMySql(
                "server=127.0.0.1;port=3306;user=root;password=;database=Issue1089",
                b => b
                    .ServerVersion("10.4.10-mariadb")
                    .DefaultDataTypeMappings(m => m
                        .WithClrDateTime(MySqlDateTimeType.DateTime))) // This makes no difference
            .UseLoggerFactory(
                LoggerFactory.Create(
                    b => b
                        .AddConsole()
                        .AddFilter(level => level >= LogLevel.Information)))
            .EnableSensitiveDataLogging()
            .EnableDetailedErrors();
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
    }
}

internal class Program
{
    private static void Main()
    {
        using (var context = new Context())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            context.IceCreams.Add(
                new IceCream
                {
                    IceCreamId = 1,
                    Name = "Vanilla",
                    BestServedBefore = DateTime.MinValue,
                });

            context.SaveChanges();
        }

        using (var context = new Context())
        {
            var iceCreams = context.IceCreams.ToList();

            Debug.Assert(iceCreams.Count == 1);
            Debug.Assert(iceCreams[0].BestServedBefore == DateTime.MinValue);
        }
    }
}

}

</details>

It generates the following queries:

<details>
<summary>SQL</summary>

```sql
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 3.1.3 initialized 'Context' using provider 'Pomelo.EntityFrameworkCore.MySql' with options: ServerVersion 10.4.10 MariaDb SensitiveDataLoggingEnabled DetailedErrorsEnabled

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE DATABASE `Issue1089`;

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (19ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE `IceCreams` (
          `IceCreamId` int NOT NULL AUTO_INCREMENT,
          `Name` longtext CHARACTER SET utf8mb4 NULL,
          `BestServedBefore` datetime NOT NULL,
          CONSTRAINT `PK_IceCreams` PRIMARY KEY (`IceCreamId`)
      );

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (12ms) [Parameters=[@p0='1', @p1='0001-01-01T00:00:00' (DbType = DateTime), @p2='Vanilla' (Size = 4000)], CommandType='Text', CommandTimeout='30']
      INSERT INTO `IceCreams` (`IceCreamId`, `BestServedBefore`, `Name`)
      VALUES (@p0, @p1, @p2);

info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 3.1.3 initialized 'Context' using provider 'Pomelo.EntityFrameworkCore.MySql' with options: ServerVersion 10.4.10 MariaDb SensitiveDataLoggingEnabled DetailedErrorsEnabled

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT `i`.`IceCreamId`, `i`.`BestServedBefore`, `i`.`Name`
      FROM `IceCreams` AS `i`

The database table looks like this:

IceCreamId | Name | BestServedBefore
-- | -- | --
1 | Vanilla | 0001-01-01 00:00:00

You can see that even though 0001-01-01 00:00:00 is not in the MySQL's officially supported datetime range, it is saved and retrieved correctly. This is also true for dates higher then DateTime.MinValue but lower than the official lower bound of 1000-01-01 00:00:00 (e.g. DateTime.MinValue.AddDays(32) will work as well).

---

Your issue is not a bug in Pomelo or EF Core, but a misconception in your code.

The from_unixtime() function states in the official docs:

Returns a representation of the unix_timestamp argument as a value in YYYY-MM-DD hh:mm:ss or YYYYMMDDhhmmss format, depending on whether the function is used in a string or numeric context. unix_timestamp is an internal timestamp value representing seconds since 1970-01-01 00:00:00 UTC, such as produced by the UNIX_TIMESTAMP() function.

So as you already suspected above, your issue is related to your usage of from_unixtime().

The reason is, that you are using a non-nullable DateTime property in your code.

Because of that, whenever EF Core sees the default value of a System.DateTime, which is DateTime.MinValue, it will assume you want to use the default value specified for the table field, which in your case will be from_unixtime(0), and will not insert any value at all, so the database table can handle the default value itself, which in your case will lead to 1970-01-01 00:00:00 being inserted into the table.

If you want to keep from_unixtime(0) as the default in the database table, but still be able to insert DateTime.MinValue in the table, you will need to change your property definition in your code from public DateTime SomeDateTime { get; set; } to public DateTime? SomeDateTime { get; set; } and make it nullable.

To clarify, the table field does not have to be nullable on the database side, only the property in your code has to be. When it it nullable, the default value for the property is not DateTime.MinValue anymore, but null instead.

Therefore, if you then set SomeDateTime to DateTime.MinValue, EF Core will actually send DateTime.MinValue to the database. If you set SomeDateTime to null, then from_unixtime(0) will be used (because null is the default for a nullable property).

The following sample code demonstrates this:

Sample code

```c#
using System;
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Pomelo.EntityFrameworkCore.MySql.Internal;

namespace IssueConsoleTemplate
{
public class IceCream
{
public int IceCreamId { get; set; }
public string Name { get; set; }
public DateTime? BestServedBefore { get; set; } // <-- nullable
public DateTime BestServedBeforeMin1970 { get; set; }
}

public class Context : DbContext
{
    public DbSet<IceCream> IceCreams { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseMySql(
                "server=127.0.0.1;port=3306;user=root;password=;database=Issue1089_01",
                b => b
                    .ServerVersion("10.4.10-mariadb")
                    .DefaultDataTypeMappings(
                        m => m
                            .WithClrDateTime(MySqlDateTimeType.DateTime))) // This makes no difference
            .UseLoggerFactory(
                LoggerFactory.Create(
                    b => b
                        .AddConsole()
                        .AddFilter(level => level >= LogLevel.Information)))
            .EnableSensitiveDataLogging()
            .EnableDetailedErrors();
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<IceCream>(
            entity =>
            {
                entity.Property(e => e.BestServedBefore)
                    .IsRequired() // <-- we only want the property to be nullable, not the table field
                    .HasDefaultValueSql("from_unixtime(0)");

                entity.Property(e => e.BestServedBeforeMin1970)
                    .HasDefaultValueSql("from_unixtime(0)");

                entity.HasData(
                    new IceCream
                    {
                        IceCreamId = 1,
                        Name = "Vanilla",
                        BestServedBefore = DateTime.MinValue,
                        BestServedBeforeMin1970 = DateTime.MinValue,
                    },
                    new IceCream
                    {
                        IceCreamId = 2,
                        Name = "Chocolate",
                        BestServedBefore = null,
                        BestServedBeforeMin1970 = DateTime.MinValue,
                    });
            });
    }
}

internal class Program
{
    private static void Main()
    {
        using var context = new Context();

        context.Database.EnsureDeleted();
        context.Database.EnsureCreated();

        var iceCreams = context.IceCreams
            .OrderBy(i => i.IceCreamId)
            .ToList();

        Debug.Assert(iceCreams.Count == 2);
        Debug.Assert(iceCreams[0].BestServedBefore.HasValue);
        Debug.Assert(iceCreams[0].BestServedBefore == DateTime.MinValue);
        Debug.Assert(iceCreams[0].BestServedBeforeMin1970.Year > 1969);
        Debug.Assert(iceCreams[1].BestServedBefore.HasValue);
        Debug.Assert(iceCreams[1].BestServedBefore.Value.Year > 1969);
        Debug.Assert(iceCreams[1].BestServedBeforeMin1970.Year > 1969);
    }
}

}

</details>

It generates the following queries:

<details>
<summary>SQL</summary>

```sql
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 3.1.3 initialized 'Context' using provider 'Pomelo.EntityFrameworkCore.MySql' with options: ServerVersion 10.4.10 MariaDb SensitiveDataLoggingEnabled DetailedErrorsEnabled

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (10ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      DROP DATABASE `Issue1089_01`;

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE DATABASE `Issue1089_01`;

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (10ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE `IceCreams` (
          `IceCreamId` int NOT NULL AUTO_INCREMENT,
          `Name` longtext CHARACTER SET utf8mb4 NULL,
          `BestServedBefore` datetime NOT NULL DEFAULT from_unixtime(0),
          `BestServedBeforeMin1970` datetime NOT NULL DEFAULT from_unixtime(0),
          CONSTRAINT `PK_IceCreams` PRIMARY KEY (`IceCreamId`)
      );

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      INSERT INTO `IceCreams` (`IceCreamId`, `BestServedBefore`, `Name`)
      VALUES (1, '0001-01-01 00:00:00', 'Vanilla');

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      INSERT INTO `IceCreams` (`IceCreamId`, `Name`)
      VALUES (2, 'Chocolate');

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT `i`.`IceCreamId`, `i`.`BestServedBefore`, `i`.`BestServedBeforeMin1970`, `i`.`Name`
      FROM `IceCreams` AS `i`
      ORDER BY `i`.`IceCreamId`

The database table looks like this:

IceCreamId | Name | BestServedBefore | BestServedBeforeMin1970
-- | -- | -- | --
1 | Vanilla | 0001-01-01 00:00:00 | 1970-01-01 01:00:00
2 | Chocolate | 1970-01-01 01:00:00 | 1970-01-01 01:00:00

---

This is a variation of the misconception described in #989.

[RheaAyase]

In other words, you're saying that "it's a feature" and "not a bug" huh? I'd assume that when I say something=anything, I want that anything to be written into the something, hence why I see this as a bug. This is rather funny behavior.

[lauxjpn]

In other words, you're saying that "it's a feature" and "not a bug" huh?

@RheaAyase It is neither. The issue is not with EF Core, but with your code. Basically, you are doing it wrong.

And I don't even mean, that in practice, your probably don't want to use a column default that returns 1970-01-01 but use 0001-01-01 in your app code (which in most cases will make little sense; but I don't know your concrete scenario).

What I mean is, that it seems you want to use a default value that has been defined for a table column in the database, and the database type is non-nullable, and you then map it to a non-nullable .NET type. That is fine.
Then you want to use DateTime.MinValue in some cases, but want to use from_unixtime(0) in others. That is also fine, but you need to give EF Core something to differentiate between those two scenarios.
EF Core needs to know, when you want to actually input the DateTime.MinValue and when default(DateTime) (which are both the same value) should actually be ignored to use the default value defined for the database column instead.

So to differentiate between those two, you can make the property nullable. That results in DateTime.MinValue being send as a value to the table, and the default(DateTime?) value (which is null and therefore distinct from DateTime.MinValue) being ignored (which results in the default value defined for the database column being used instead).

I do understand however, that this behavior can be surprising in certain cases, when encountered for the first time, but when taking a closes look at it, it makes a lot of sense.

For further information on this, see Generated Values: Value generated on add and Setting Explicit Values for Generated Properties: Saving an explicit value during add in the official EF Core docs:

If you add an entity to the context that has a value assigned to the property, then EF will attempt to insert that value rather than generating a new one. A property is considered to have a value assigned if it is not assigned the CLR default value (null for string, 0 for int, Guid.Empty for Guid, etc.). For more information, see Explicit values for generated properties.

[RheaAyase]

You don't have to be patronizing, I'm not an idiot. I understand the problem - I just tried to also explain the user's point of view - a user (like just about 99.999% of users) who doesn't know how EFCore decides when to use database generated default value. This point of view is, that the moment I assign anything, I expect it to make it into the database, without realizing that when it's default value, EFCore will use database defaults instead of listening to my assignment.

[lauxjpn]

You don't have to be patronizing

I am not trying to be patronizing. I am spending a lot of time today to make sure, that you understand the inner workings of this issue (which you did not before, which is why you reported this issue in the first place).

Also, this is not just about you, but also about anybody of the community, who might stumble across this or a similar issue in the future. Many community members are not native englisch speakers (like me), and I try to answer in a way, that most users, even those with little development experience, can follow (that is also the reason, why in nearly all cases I also post fully working sample code).

This point of view is, that the moment I assign anything, I expect it to make it into the database

I do understand that. I posted the links to the docs above, and they explain, why the behavior is the way it is.

Feel free to open an issue on the EF Core repo, if you have a good alternative how to implement the default value behavior or a mitigation strategy.