CODEWITHSUNDEEP
c#sqlsqlitedatetimetime
AngryHacker
AngryHacker

Reputation: 61646

Datetimes and .NET with SQLite

I have a table:

CREATE TABLE [Lines] (
[Value] TEXT  NOT NULL,
[AddedOn] TIMESTAMP DEFAULT CURRENT_TIMESTAMP NULL
)

As you can see, the AddedOn column is a timestamp and is set to record the current datetime if one is not provided at insert time.

Please consider the following c# code:

using (var cmd = conn.CreateCommand())    
{
    cmd.CommandText = "INSERT INTO Lines(Value) VALUES (@Value)";
    cmd.Parameters.AddWithValue("@Value", objectValue);
    cmd.ExecuteNonQuery();
}

Note that above I am letting SQLite assign the date. Now, the same code, except I am passing the AddedOn value (e.g. DateTime.Now - right now) 

using (var cmd = conn.CreateCommand())    
{
    cmd.CommandText = "INSERT INTO Lines(Value, AddedOn) VALUES (@Value, @AddedOn)";
    cmd.Parameters.AddWithValue("@Value", objectValue);
    cmd.Parameters.AddWithValue("@AddedOn", DateTime.Now);

    cmd.ExecuteNonQuery();
}

If I then compare the results of these 2 inserts, I find that when I let the AddedOn default kick in (first example), it saved the current datetime at the GMT. When I passed the date explicitly (2nd example), it saved the actual current datetime in my timezone.

Is this by design? Is it a bug? It seems like the behavior should be consistent and the datetime I pass in should be converted to GMT.

Upvotes: 1

Views: 5755

Answers (1)

Merlyn Morgan-Graham
Merlyn Morgan-Graham

Reputation: 59151

Is it a bug?

Not sure, but I'd be more surprised if this didn't accomplish your goal:

cmd.Parameters.AddWithValue("@AddedOn", DateTime.UtcNow);

To me, the behavior you're experiencing makes sense.

I wouldn't imagine a TIMESTAMP column would have any information on whether a time is expected to be in UTC or not, and I certainly wouldn't expect it to force UTC by default.

This will also make for better perf, because time zone conversions are (relatively) expensive, and the automated conversion would be a hidden mechanism.

Upvotes: 4

Related Questions