ORM lite MySql and Noda time

1

I was looking into Noda time I was wondering if ServiceStack supports it and how a ZonedDateTime would be persisted to MySql using ORM lite.

Are there any special considerations when using Noda time?

I see there’s a NodaTime.Serialization.ServiceStackText package.

Any pointers on how to use it with ServiceStack would be great or if it’s better to avoid it.

2

I created a class to test this:


public class DateTest
{
    [Required]
    [CheckConstraint(nameof(StartEpochMs) + " > 0")]
    public long StartEpochMs { get; set; }
    
    public DateTime Start { get; set; }

    [CheckConstraint(nameof(EndEpochMs) + " > 0")]
    public long EndEpochMs { get; set; }
    
    public TimeZoneInfo TimeZone { get; set; }
    
    public ZonedDateTime Zoned { get; set; }
    
    public LocalDateTime Local { get; set; }
}

I’ve been trying to persist this object :

DateTimeZoneProviders.Tzdb
                .CreateDefaultSerializersForNodaTime()
                .ConfigureSerializersForNodaTime();

var rawDb = rawDbFactory.Open();

rawDb.DropAndCreateTable<DateTest>();

DateTest dt = new DateTest();
dt.StartEpochMs = stamp.StartEpochMs;
dt.EndEpochMs = stamp.EndEpochMs;
dt.TimeZone = _timeZoneInfo;
dt.Zoned = new ZonedDateTime(Instant.FromUnixTimeMilliseconds(stamp.StartEpochMs),DateTimeZoneProviders.Tzdb["America/Vancouver"]);
dt.Local = new LocalDateTime(stamp.Start.Year,stamp.Start.Month,stamp.Start.Day,stamp.Start.Hour,stamp.Start.Minute);
dt.Start = stamp.Start;
rawDb.Insert(dt);

I’m getting this exception:

System.FormatException: String ‘2020-09-29T03:49:12 America/Vancouver (-07)’ was not recognized as a valid DateTime.
at System.DateTimeParse.Parse(ReadOnlySpan`1 s, DateTimeFormatInfo dtfi, DateTimeStyles styles, TimeSpan& offset)
at System.DateTimeOffset.Parse(String input, IFormatProvider formatProvider, DateTimeStyles styles)
at System.DateTimeOffset.Parse(String input, IFormatProvider formatProvider)
at ServiceStack.Text.Common.DateTimeSerializer.ParseDateTimeOffset(String dateTimeOffsetStr) in C:\BuildAgent\work\912418dcce86a188\src\ServiceStack.Text\Common\DateTimeSerializer.

This is a console application and I’m using orm lite to persist some data.

3

OrmLite has no dependency on NodaTime (nor do any of the underlying ADO.NET Data Providers) so it’s unable to save its values natively & will require registering a custom Type Converter to tell OrmLite how to handle it. Here’s an example Type Converter for SQL Server:

SQL Server Converter for NodaTime Instant Type in DATETIMEOFFSET with TimeZone:

public class SqlServerInstantConverter : OrmLiteConverter
{
    public override string ColumnDefinition => "DATETIMEOFFSET";

    public override DbType DbType => DbType.DateTimeOffset;

    public override object ToDbValue(Type fieldType, object value)
    {
        var instantValue = (Instant)value;
        return instantValue.ToDateTimeOffset();
    }

    public override object FromDbValue(Type fieldType, object value)
    {
        return Instant.FromDateTimeOffset((DateTimeOffset)value);
    }
}

SqlServerDialect.Provider.RegisterConverter<Instant>(
    new SqlServerInstantConverter());

In DATETIME2 in UTC:

public class SqlServerInstantDateTimeConverter : OrmLiteConverter
{
    public override string ColumnDefinition => "DATETIME2";

    public override DbType DbType => DbType.DateTime;

    public override object ToDbValue(Type fieldType, object value)
    {
        var instantValue = (Instant) value;
        return instantValue.ToDateTimeUtc();
    }

    public override object FromDbValue(Type fieldType, object value)
    {
        var dateTime = DateTime.SpecifyKind((DateTime)value, DateTimeKind.Utc);
        return Instant.FromDateTimeUtc(dateTime);
    }
}

SqlServerDialect.Provider.RegisterConverter<Instant>(
    new SqlServerInstantDateTimeConverter());
4

Thanks. That’s helpful!