1

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.

AngryHacker
  • 59,598
  • 102
  • 325
  • 594

1 Answers1

4

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.

Merlyn Morgan-Graham
  • 58,163
  • 16
  • 128
  • 183
  • I disagree about perf because when I retrieve the data I still have to convert it to a local datetime. – AngryHacker Nov 11 '11 at 21:09
  • @AngryHacker: If you store in one format and retrieve in another format you must do a conversion, so the perf hit is unavoidable. However the DB doesn't *force* a perf hit on you, so you won't have to undergo two conversions. This means you are free to store local time if you wish. – Merlyn Morgan-Graham Nov 11 '11 at 22:08
  • 1
    @AngryHacker: Does your question then become: "How do I get SqLite to store `CURRENT_TIMESTAMP` in local time"? If so, see this question: http://stackoverflow.com/questions/381371/sqlite-current-timestamp-is-in-gmt-not-the-timezone-of-the-machine - there are two sets of advice here: 1: Always use UTC, and only do the conversion on presentation (my personal recommendation) 2: Use a mechanism other than `CURRENT_TIMESTAMP` (I don't recommend this as you will paint yourself into a corner in the future. Math is MUCH harder and more expensive when TZ and DST comes into play in the middle). – Merlyn Morgan-Graham Nov 11 '11 at 22:14
  • I ended up simply passing AddedOn manually everytime. It's much simpler because it stores the values in current time and there is no conversion to anything anywhere whatsoever. – AngryHacker Nov 12 '11 at 00:41