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
{
[PrimaryKey]
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.DropAndCreateTable<Datebug>();
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.DropAndCreateTable<Datebug>();
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);
