Reading DateTime ticks from DB is converting it to int (and therefore getting the wrong value)

[yhorowitz]

I have the following query that insert a DateTime object into the table:

using (SQLiteConnection Connection = new SQLiteConnection(new SQLitePlatformGeneric(), "mydbpath"))
{
    Connection.Execute("INSERT INTO punch(user_id, punch_type, punch_time, punch_comment)  VALUES(@user_id, @punch_type, @punch_time, @punch_comment)", new object[] { 1, "in", DateTime.UtcNow, "" });
}

I then use the following code to read the date data:

using (SQLiteConnection Connection = new SQLiteConnection(new SQLitePlatformGeneric(), db.ConnectionString))
{
    SQLiteCommand cmd = Connection.CreateCommand("SELECT punch_time FROM punch WHERE user_id = @user_id", new object[] { 1 });
    var result = cmd.ExecuteDeferredQuery();
}

The data gets written into the database well with 'punch_time' being 636439764482923774.

However, when I get the data back it shows up with the incorrect value of 49290494. This is the value an int would have if it was casted from the long value of the expected date ticks value. It appears the the ExecutedDeferredQuery method is casting it into an int before returning it.

[yhorowitz]

I was able to get around this by setting the type for the field as TEXT (prior to this I was setting it as NUMERIC). However, the value shouldn't be converted to an int if it is numeric as it is capable of holding up to 8 bytes in the database and so I believe this is a bug.

[MelbourneDeveloper]

Thanks for your detailed issue. I've experienced this before. In fact, I never really got DateTimes working correctly with the recommended approaches. It's something I want to get right at some point so I will leave this ticket open and do some more investigation. If you want to change the code, feel free to fork the branch, make the change and send me a pull request.

Meantime, this is how we handle dates. It's done in code outside of the SQLite library. We store dates as text. I personally prefer this method instead of storing ticks because it means that if you open the database with an external editor, you can view the dates. It's not as efficient, but it certainly makes maintaining and testing the data a lot easier.

    private const string _ISOStandardDate = "yyyy-MM-dd HH:mm:ss.fff";

    private SQLiteCommandResult GetData(string commandText, IList<DataParameter> parameters, TableInfo tableInfo)
    {
        var command = CreateCommand(commandText, parameters);
        var commandResult = command.ExecuteDeferredQuery();

        if (tableInfo == null)
        {
            return commandResult;
        }

        foreach (var column in tableInfo.ColumnsByName)
        {
            foreach (var row in commandResult.Data)
            {
                if (!row.ContainsKey(column.Key))
                {
                    continue;
                }

                var theValue = row[column.Key];
                row[column.Key] = SQLiteDataTypeConvert(theValue, column.Value.PropertyInformation.PropertyType);
            }
        }

        return commandResult;
    }

    private static object SQLiteDataTypeConvert(object value, Type propertyType)
    {
        if (value == null)
        {
            return null;
        }

        var valueAsString = value as string;

        if (propertyType == typeof(DateTime))
        {
            return DateTime.ParseExact(valueAsString, _ISOStandardDate, CultureInfo.InvariantCulture);
        }

        if (propertyType == typeof(Guid))
        {
            var guidAsString = valueAsString;
            var guid = Guid.Parse(guidAsString);
            return guid;
        }

        if (propertyType == typeof(bool))
        {
            var boolAsInteger = (int)value;
            var theBool = boolAsInteger == 1;
            return theBool;
        }

        if (propertyType != typeof(decimal))
        {
            return value;
        }

        var decimalAsString = valueAsString;
        var theDecimal = decimal.Parse(decimalAsString);
        return theDecimal;
    }

PS: This is how the original code seems to handle DateTimes:

        if (clrType == typeof(DateTime))
        {
            return _conn.StoreDateTimeAsTicks ? new DateTime(_sqlitePlatform.SQLiteApi.ColumnInt64(stmt, index), DateTimeKind.Utc) : DateTime.Parse(_sqlitePlatform.SQLiteApi.ColumnText16(stmt, index), CultureInfo.InvariantCulture);
        }

In ReadCol of SQLiteCommand.

[MelbourneDeveloper]

@yitzih

I realised that I didn't pay attention to this issue properly.

The data gets written into the database well with 'punch_time' being 636439764482923774.

However, when I get the data back it shows up with the incorrect value of 49290494. This is the value an int would have if it was casted from the long value of the expected date ticks value. It appears the the ExecutedDeferredQuery method is casting it into an int before returning it.

This is probably a problem in and of itself. Any chance you could create a new ticket about not being able to use long data types? I'll get to this at some point in the future, but I don't have time right now.