OrmLite DateTimeConverter not working

ServiceStack.OrmLite
1

I have a sqlite database with this schema:

ActivityId GUID
Platform TEXT
PackageName TEXT
ExpirationTime DATETIME

some example data looks like this:

x_exe_path {7c5a40ef-a0fb-4bfc-874a-c0f2e0b9fa8e}\epic games\launcher\portal\binaries\win64\epicgameslauncher.exe 1527705308
host 1527705308
x_exe_path {7c5a40ef-a0fb-4bfc-874a-c0f2e0b9fa8e}\epic games\launcher\portal\binaries\win64\epicgameslauncher.exe 1527705323
packageid {7c5a40ef-a0fb-4bfc-874a-c0f2e0b9fa8e}\epic games\launcher\portal\binaries\win64\epicgameslauncher.exe 1527705323

notice ExpirationTime is being stored as epoch. i want to convert this to a real date time, but it isnt working.

class:

public class ActivityPackageId
{
    [PrimaryKey]
    public Guid ActivityId { get; set; }
    public string Platform { get; set; }
    public string PackageName { get; set; }
    
    public DateTime ExpirationTime { get; set; }

    public override string ToString()
    {
        return $$"Platform: {Platform} PackageName: {PackageName} Expire date: {ExpirationTime}";
    }
}

My code:

 var dbFactory = new OrmLiteConnectionFactory(
        _fluentCommandLineParser.Object.File,  
        SqliteDialect.Provider);

SqliteDialect.Provider.RegisterConverter<DateTime>(new EpochConverter());

using (var db = dbFactory.OpenDbConnection())
{
    var activityPackageId = db.Select<ActivityPackageId>();
}

public class EpochConverter : DateTimeConverter
{
    public override string ColumnDefinition => "DATETIME";

    public override DbType DbType => DbType.DateTime;

    public override object FromDbValue(Type fieldType, object value)
    {
        var dateTime = DateTimeOffset.FromUnixTimeSeconds((long) value);

        return dateTime;
    }

    public override string ToQuotedString(Type fieldType, object value)
    {
        return base.ToQuotedString(fieldType, value);
    }

    public override object GetValue(IDataReader reader, int columnIndex, object[] values)
    {
        return base.GetValue(reader, columnIndex, values);
    }

    public override object ToDbValue(Type fieldType, object value)
    {
        return base.ToDbValue(fieldType, value);
    }
}

setting breakpoints on all the methods, only GetValue seems to be getting fired, but when i step thru, i get this error:

System.FormatException
HResult=0x80131537
Message=String was not recognized as a valid DateTime.
Source=mscorlib
StackTrace:
at System.DateTimeParse.ParseExactMultiple(String s, String[] formats, DateTimeFormatInfo dtfi, DateTimeStyles style)
at System.Data.SQLite.SQLiteConvert.ToDateTime(String dateText, SQLiteDateFormats format, DateTimeKind kind, String formatString)
at System.Data.SQLite.SQLite3.GetDateTime(SQLiteStatement stmt, Int32 index)
at System.Data.SQLite.SQLite3.GetValue(SQLiteStatement stmt, SQLiteConnectionFlags flags, Int32 index, SQLiteType typ)
at System.Data.SQLite.SQLiteDataReader.GetValue(Int32 i)
at ServiceStack.OrmLite.OrmLiteConverter.GetValue(IDataReader reader, Int32 columnIndex, Object[] values)
at WxTCmd.EpochConverter.GetValue(IDataReader reader, Int32 columnIndex, Object[] values) in D:\Code\WxTCmd\WxTCmd\Program.cs:line 220
at ServiceStack.OrmLite.OrmLiteWriteCommandExtensions.PopulateWithSqlReader[T](T objWithProperties, IOrmLiteDialectProvider dialectProvider, IDataReader reader, Tuple`3[] indexCache, Object[] values)

What i need to do is convert the epoch date to ideally a DateTimeOffset in UTC.

What am i missing?

2

in playing around with this a bit more, i managed to get it working with this code:

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

public override object GetValue(IDataReader reader, int columnIndex, object[] values)
{
    if (reader.IsDBNull(columnIndex))
    {
        return null;
    }

    var val = reader.GetInt32(columnIndex);

    if (val == 0)
    {
        return null;
    }
    return DateTimeOffset.FromUnixTimeSeconds(val).ToUniversalTime();
}

i am using it for DateTimeOffset and DateTimeOffset?

dunno if a default “from epoch” converter makes sense

3

This Exception is happening within the SQLite ADO.NET provider where it tries to read the column value as a DateTime but it fails at the first hurdle where it can’t read the data through the ADO.NET IDataReader.GetValue() API or the IDataReader.GetDateTime() API - this may be an incomplete feature with SQLite’s ADO.NET provider since storing dates as an INTEGER is one of the SQLie conventions.

I wouldn’t inherit from DateTimeConverter it doesn’t handle any of the nuances do to with with System.Data.SQLite.Core DateTime handling. I’d look at inheriting from SqliteSystemDataDateTimeConverter instead then override the parts you need, but if you want to also save it back as an int epoch I don’t think any of the existing Converters are suitable and you should likely inherit from the base OrmLiteConverter and handle all conversions yourself.

Since calling GetValue() to try get the value as an object and GetDateTIme() to try get the value as a DateTime both fail I would try to get the value as an integer directly, e.g:

public override object GetValue(IDataReader reader, int columnIndex, object[] values)
{
    var intValue = reader.GetInt32(columnIndex);
}

BTW SQLite doesn’t have a DATETIME Type, your column definition should likely be INTEGER instead.

4

Epoch’s are always UTC so I’m not sure ToUniversalTime() is necessary unless DateTimeOffset impl is automatically converting it into a local time. ServiceStack.Text has its own int.FromUnixTime() extension method for handling int epoch DateTime’s for serialization.