Bug in Sqlite GetDateTimeConverter

GetDateTimeConverter for Sqlite behaves differently than MySql.

If you use DateStyle of UTC, create a UTC DateTime, save it, read it back, it gets shifted by the localtime offset.

using System;
using System.Data;

using ServiceStack;
using ServiceStack.Testing;
using ServiceStack.OrmLite;
using ServiceStack.DataAnnotations;

namespace ConsoleApp1
  public class Datebug
    public int Id { get; set; }
    public DateTime MyDate { get; set; }

  class Program
    static void Main(string[] args)
      var host = new BasicAppHost().Init();
      var container = host.Container;

      var date = new DateTime(2017, 1, 1, 0, 0, 0, DateTimeKind.Utc);
      Console.Out.WriteLine($ "Date: {date.ToString("yyyy-MM-dd HH:mm:ss zzz")}");

      var dbf1 = new OrmLiteConnectionFactory(":memory:", SqliteDialect.Provider);
      SqliteDialect.Provider.GetDateTimeConverter().DateStyle = DateTimeKind.Utc;
      using (var db1 = dbf1.Open())
        db1.Insert<Datebug>(new Datebug { Id = 1, MyDate = date });
        var date1 = db1.SingleWhere<Datebug>("Id", 1).MyDate;
        Console.Out.WriteLine($ "SQlite: {date1.ToString("yyyy-MM-dd HH:mm:ss zzz")}");

      var dbf2 = new OrmLiteConnectionFactory("Server=localhost; Database=db1; Uid=db1; Pwd=db1; SslMode=None;", MySqlDialect.Provider);
      MySqlDialect.Provider.GetDateTimeConverter().DateStyle = DateTimeKind.Utc;
      using (var db2 = dbf2.Open())
        db2.Insert<Datebug>(new Datebug { Id = 1, MyDate = date });
        var date2 = db2.SingleWhere<Datebug>("Id", 1).MyDate;
        Console.Out.WriteLine($ "MySQL: {date2.ToString("yyyy-MM-dd HH:mm:ss zzz")}");

This outputs

Date: 2017-01-01 00:00:00 +00:00
Sqlite: 2017-01-01 08:00:00 +00:00
MySQL: 2017-01-01 00:00:00 +00:00

The Sqlite date is 8 hours off (because I am in PST).

should be

        dateTime = dateTime.Kind == DateTimeKind.Local ? dateTime.ToUniversalTime() : DateTime.SpecifyKind(dateTime, DateTimeKind.Utc);

It’s not as easy as that, not converting Unspecified DateTime’s breaks a number of existing OrmLite.Tests. The current behavior gets close to consistent behavior as other RDBMS’s as we could.

If you can make a change that doesn’t break any tests we can look at changing the current behavior. Otherwise you’d need to register a custom SqliteSystemDataDateTimeConverter with your preferred impl.

Are you sure those tests are right?

insert_and_select_utc_DateTime() is failing because it’s writing a UTC date when DateStyle is Unspecified, and reading back expecting a localtime. That’s not going to happen. You are going to get an unspecified with the same value as the input.

insert_and_select_local_DateTime is claiming it should receive a time converted from local to universal. But it won’t, it will be a the time just with Utc kind

I copied the UtcDateTests from the SqliteTest project into a MySQL Test project and they fail there too because the values the tests expect aren’t correct.

Here’s a grid of all the combinations and what’s currently happening. Setting DateStyle to Unspecified, Local and UTC and passing in an unspecified, Local and UTC. I used a date value of 2017-01-01 00:00:00. Green means matching and red is where it is different.

MSSQL and MySQL match in what they return, Currently Sqlite returns different values when using the DateStyle = UTC. Surely they need to be the same.

You said the other test need to pass, but are they just testing for the wrong answers?

The tests are in the ServiceStack.OrmLite.Tests project I linked to earlier, this same test suite is run against each RDBMS provider by changing the DefaultDialect.

It does seem the issue only happens in core.

When I run my test cases against ServiceStack,OrmLite 4.5.x, they fail.

When I run them again ServiceStack.Ormlite.Core 1.0.44, they pass.

I can’t work out how to build and run the Ormlite.Core unit tests. Can you point me at anything?

Ok, I got it working.

ServiceStack.OrmLite.Tests fails 36 tests when it is built as a netcore unit test app. Some are licensing issues, not sure why, I tried to put my key in there. But several look like this Sqlite issue.

If I put in my line above, only 10 tests fail, but they don’t look related.

Checking if there any resolution?

A lot of ServiceStack.OrmLite.Tests unit tests fail when it is run as a dotnet core2 app, but work when it is run as 4.5. Are you seeing this too?

How do you run tests in .net core 2?

Here are the build steps to run OrmLite tests on .net core

export FrameworkPathOverride=/usr/lib/mono/4.5/
export SQL_SERVER_BUILD_DB="Server=buildserver;Database=test;User Id=test;Password=...;"

dotnet restore /p:NUNITLITE=1 src/ServiceStack.OrmLite.sln
dotnet build /p:NUNITLITE=1 src/ServiceStack.OrmLite.sln
cd tests/ServiceStack.OrmLite.Tests
dotnet exec bin/Release/netcoreapp2.0/ServiceStack.OrmLite.Tests.dll --labels=All

with these settings all tests are passed with .NET Core on Linux

I had to add a line in each csproj to ensure targeting netstandard or netcoreapp project when building (/p:TargetFrameworks doesn’t seem to work)


<TargetFrameworks Condition="'$(OS)' != 'Windows NT'">netstandard1.3<TargetFrameworks>


<TargetFrameworks Condition="'$(OS)' != 'Windows NT'">netcoreapp2.0</TargetFrameworks>

Add the Microsoft Net Test and NUnit packages into tests/ServiceStack.OrmLite.Tests/ServiceStack.OrmLite.Tests.csproj

<PackageReference Include="Microsoft.NET.Test.Sdk" Version="15.5.0-preview-20170914-09" />
<PackageReference Include="NUnit" Version="3.6.1" />
<PackageReference Include="NUnit3TestAdapter" Version="3.8.0" />

Switch OrmLiteTestBase.cs to Sqllite


dotnet restore src/ServiceStack.OrmLite.sln
dotnet build src/ServiceStack.OrmLite.sln

Run tests

dotnet test tests/ServiceStack.OrmLite.Tests

or just run the DateTime ones that highlights this issue

dotnet test --filter FullyQualifiedName~DateTime tests/ServiceStack.OrmLite.Tests

Microsoft ® Test Execution Command Line Tool Version 15.3.0-preview-20170628-02
Copyright © Microsoft Corporation. All rights reserved.

Starting test execution, please wait…
NUnit Adapter Test execution started
Running all tests in /mnt/f/Temp/ss/ServiceStack.OrmLite/tests/ServiceStack.OrmLite.Tests/bin/Debug/netcoreapp2.0/ServiceStack.OrmLite.Tests.dll
NUnit3TestExecutor converted 856 of 856 NUnit test cases
Failed Does_return_UTC_Dates_with_UTC_DateStyle
Error Message:
Expected: 2012-01-01 17:01:01
But was: 2012-01-01 09:01:01

Stack Trace:
at ServiceStack.OrmLite.Tests.DateTimeTests.Does_return_UTC_Dates_with_UTC_DateStyle() in /mnt/f/Temp/ss/ServiceStack.OrmLite/tests/ServiceStack.OrmLite.Tests/DateTimeTests.cs:line 112

NUnit Adapter Test execution complete

Total tests: 23. Passed: 22. Failed: 1. Skipped: 0.
Test Run Failed.
Test execution time: 4.6435 Seconds

Do you have issue only with Does_return_UTC_Dates_with_UTC_DateStyle? Or other tests are failed too? If issue happens with this test only can you say which locale and timezone settings you use on testing machine?

I’m running in PDT but en-GB locale:

$ date +'%:z %Z'
-07:00 DST

$ locale

These are the full results


I have been running tests using the NUnit test adaptor. But even now building and running the tests on Windows with your instructions fail for me.

With a new clone, and changing OrmLiteTestBase to use Sqlite, then:

dotnet restore /p:NUNITLITE=1 src/ServiceStack.OrmLite.sln
dotnet build /p:NUNITLITE=1 src/ServiceStack.OrmLite.sln
dotnet exec tests/ServiceStack.OrmLite.Tests/bin/Debug/netcoreapp2.0/ServiceStack.OrmLite.Tests.dll --labels=All

Has similar fails. https://pastebin.com/GLA5f98k

As an aside, I have never successfully managed to run the whole test suite. I’ve tried SqlServer and MySql too. They all have tests that fail. Either I’m doing something wrong or there is some setup voodoo that’s missing.

MySQL results
SqlServer2012 results

The only external environment requirement for PostgreSQL and SQL Server. should be to create a Schema called Schema, otherwise I’m able to run the entire test suite in each supported db using the connection strings configured in OrmLiteTestBase.

I’ve just re-run the ServiceStack.OrmLite.Tests against the following Dialects:

public static Dialect DefaultDialect = Dialect.Sqlite;
public static Dialect DefaultDialect = Dialect.SqlServer;
public static Dialect DefaultDialect = Dialect.MySql;
public static Dialect DefaultDialect = Dialect.PostgreSql;

and they all pass for me running .NET v4.5 using Resharper in VS.NET 2017 Community.

Yes, I also finally have got the Net45 ones to pass. My issue was I needed to build the whole ServiceStack project and replace the checkout Dlls that come in the ServiceStack.Ormlite repository.

Having a developer’s howto page would have really helped here :smile:

But running the netcoreapp2.0 tests still fail because of the SQlite issue.

tests\ServiceStack.OrmLite.Tests\bin\Debug\net45\ServiceStack.OrmLite.Tests.exe [all pass]

dotnet exec tests\ServiceStack.OrmLite.Tests\bin\Debug\netcoreapp2.0\ServiceStack.OrmLite.Tests.dll --labels=all [26 fail]

It’s like there is a breaking change in Microsoft’s Sqlite implementation when running under netcore2.0.