MySQL DateTimeOffset

[Peperud]

LockoutEnd is DateTimeOffset type in asp.net Identity, however MySQL does not have this data type and DATETIME type is used. This would lead to loss/inaccuracy unless the database is always fed say UTC dates, which in tern is taken into consideration when the value is de-serialized.

It looks like the datetimeoffset might be going in a UTC, but then is interpreted as local on the read.

I don't see any explicit handling for this? What am I missing?

[grandchamp]

I didn't make any handler for this. Have to check how it's done in MySQL implementations. Do you have any suggestion?

[Peperud]

Something like that

public class DapperMySqlDateTimeOffsetHandler : SqlMapper.TypeHandler<DateTimeOffset?>
    {
        // assume UTC in and out

        public override DateTimeOffset? Parse(object value)
        {
            if (value == null || value == DBNull.Value) return null;

            var result = DateTime.SpecifyKind((DateTime)value, DateTimeKind.Utc);
            return new DateTimeOffset(result);
        }

        public override void SetValue(IDbDataParameter parameter, DateTimeOffset? value)
        {
            parameter.DbType = DbType.DateTime;
            parameter.Value = !value.HasValue ? DBNull.Value : parameter.Value = value.Value.UtcDateTime;
        }
    }

...and then somewhere as part of a global initialization...

SqlMapper.AddTypeHandler(new YourNamespaceHere.DapperMySqlDateTimeOffsetHandler());

[grandchamp]

Sounds good. Do you wanna try to make a PR for this?

[Peperud]

@grandchamp done

[grandchamp]

84