如何从 SQLite 表中获取实际的日期时间值? [英] How can I get the actual datetime values from a SQLite table?

问题描述

我正在查询具有 DateTime 成员的 SQLite 表:

I am querying a SQLite table which has a DateTime member:

public class PhotraxBaseData
{
    [SQLite.PrimaryKey] 
    [SQLite.AutoIncrement]
    public int Id { get; set; }
    . . .
    public DateTime dateTimeTaken { get; set; }
    . . .

表中的记录包含从 2008 年到 2014 年的 dateTimeTaken 值.没有任何记录包含 null 或空的 dateTimeTaken 值.

The records in the table contain dateTimeTaken values ranging from 2008 to 2014. None of the records contain a null or empty dateTimeTaken value.

当通过 LINQPad 查询时，我得到2008-04-25 10:38:56"这个查询:

When queried via LINQPad, I get "2008-04-25 10:38:56" for this query:

SELECT MIN(dateTimeTaken) FROM PhotraxBaseData

....和2014-04-27 19:26:09"为SELECT MAX(..."

....and "2014-04-27 19:26:09" for "SELECT MAX(..."

IOW，LINQPad 给了我我所期望的.然而，当我运行这段代码时:

IOW, LINQPad gives me what I would expect. Yet, when I run this code:

dateFrom.Date = PhotraxSQLiteUtils.GetEarliestDate();
dateTo.Date = PhotraxSQLiteUtils.GetLatestDate();
 . . .
internal static DateTimeOffset GetEarliestDate()
{
    DateTimeOffset dto;
    using (var db = new SQLite.SQLiteConnection(App.DBPath))
    {
        string sql = "SELECT MIN(dateTimeTaken) FROM PhotraxBaseData";
        dto = db.ExecuteScalar<DateTimeOffset>(sql);
    }
    return dto;
}

注意:GetLatestDate() 方法与 GetEarliestDate() 方法相同，除了使用 MIN 的 MAX 插入

Note: the GetLatestDate() method is identical to the GetEarliestDate() method except for using MAX insted of MIN

...在这两种情况下，方法实际返回的是1/1/0001"，而数据控件中显示的是1/1/1914".数据控件是这样声明的:

...in both cases, what is actually returned from the methods is "1/1/0001" and what is displayed in the data controls is "1/1/1914". The data controls are declared this way:

<StackPanel Orientation="Horizontal">
    <TextBlock Text="Photos taken between">
    </TextBlock>
    <DatePicker x:Name="dateFrom">
    </DatePicker>
</StackPanel>
<StackPanel Orientation="Horizontal">
    <TextBlock Text="and">
    </TextBlock>
    <DatePicker x:Name="dateTo">
    </DatePicker>

为什么我没有得到正确的值?以及如何获取实际的 MIN 和 MAX 日期(而不是返回1/1/0001"并显示1/1/1914"，以填充 DateTimeOffset 变量和随后的 DatePicker XAML 控件?

Why am I not getting the right values? And how can I get the actual MIN and MAX dates (instead of "1/1/0001" returned and "1/1/1914" displayed, to populate the DateTimeOffset variable and subsequently the DatePicker XAML control?

注意:我在 SQLite Browser 中看到，唯一的数据类型似乎是创建 SQLite 表时可用的有 Integer、Text、BLOB、Real 和 Numeric

NOTE: I see in SQLite Browser that the only data types that seem to be available when creating a SQLite table are Integer, Text, BLOB, Real, and Numeric

所以也许我的问题是类的 DateTime 成员 (dateTimeTaken) 在 SQLite 表中没有相应的数据类型，因此被存储为...什么?文字?将类中的数据类型从 DateTime 更改为 String，然后确保将数据存储为20141103_111111"(yyyymmdd_hhmmss) 或诸如此类以进行准确排序是否合适?

So maybe my problem is that the DateTime member of the class (dateTimeTaken) does not have a corresponding data type in the SQLite table, and is thus being stored as...what? Text? Is the appropriate way to handle this to change the data type in the class from DateTime to String, and then make sure the data is stored as "20141103_111111" (yyyymmdd_hhmmss) or some such, for accurate sorting?

推荐答案

我认为您需要指定数据库返回的确切类型，然后在获得它后在客户端进行转换.

I think you need to specify the exact type being returned by the database, then convert that on the client side once you've got it.

由于返回的值可以是任何类型，所以它被装箱到一个对象中.要取消装箱，您必须首先将其强制转换为最初装箱的确切类型.然后将其转换为您需要的任何类型.

Since the value being returned could be of any type, it's boxed into an object. To unbox it, you must first cast it to the exact type that was originally boxed. Then you convert it to whatever type you need.

换句话说，如果这是您从数据库中获取的值，请指定 DateTime:

In other words, specify DateTime if that's the value you're getting from the database:

internal static DateTimeOffset GetEarliestDate()
{
    DateTimeOffset dto;
    using (var db = new SQLite.SQLiteConnection(App.DBPath))
    {
        string sql = "SELECT MIN(dateTimeTaken) FROM PhotraxBaseData";
        dto = (DateTimeOffset)db.ExecuteScalar<DateTime>(sql);
    }
    return dto;
}

另外，我不知道将值转换为 DateTimeOffset 是否是您想要做的.您可能只想将方法的返回类型更改为 DateTime.

Also, I don't know if converting the value to a DateTimeOffset is what you want to do. You might just want to change the method's return type to DateTime.

这篇关于如何从 SQLite 表中获取实际的日期时间值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！