Datetimes和.NET与SQLite [英] Datetimes and .NET with SQLite

问题描述

我有一个表：

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

如你所见， AddedOn列是一个时间戳记，并设置为记录当前的datetime（如果在插入时没有提供）。

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.

请考虑以下c＃代码：

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

请注意，以上我让SQLite分配日期。现在，相同的代码，除了我传递AddedOn值（例如DateTime.Now - 现在）

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();
}

如果我比较这两个插入的结果，我发现当我让AddedOn默认启动（第一个例子），它保存当前的datetime在GMT。当我明确地传递日期（第二个例子）时，它保存了我的时区中的实际当前日期时间。

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.

这是按设计吗？这是一个bug吗？似乎行为应该是一致的，我传入的日期时间应该转换为GMT。

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.

推荐答案

这是一个错误吗？

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.

我不会想象一个 TIMESTAMP 列将有关于时间是否预计在UTC时间的任何信息，我当然不会期望它默认强制UTC。

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.

这也将使更好的perf，因为时区转换（相对）昂贵，自动转换将是一个隐藏的机制。

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

这篇关于Datetimes和.NET与SQLite的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！