为什么通过JDBC在sqlite中使用REAL日期的datetime(..)返回不准确的超出范围的结果?

Question

我试图在Java 1.8中使用org.xerial:sqlite-jdbc:3.14.2.1在Mac上读取SQLlite db。特别是，我试着读日期，但我得到奇怪的结果。细节。

一切都很好，除了REAL类型的字段表示日期/时间。注意，SQLlite没有日期的特定类型，因此它们要么存储为

• 一个String(格式类似于YYYY-MM-DD hh:mm:ss.sss，也就是ISO-8601字符串)，
• an Integer(即:从EPOCH开始的秒数，或1970-01-01 00:00:00 UTC)
• a Real(作为儒略历日数:根据预言的公历，从公元前4714年11月24日格林威治中午开始经过的时间)。

幸运的是，SQLlite提供了像date(...)或time(...)这样的函数来解码日期字段。在我的例子中，我使用datetime(...)，它将Real转换为ISO-8601字符串。

例如，我将当前时间(实际上是2016-10-26 18:37:24.073和2016-10-26 18:37:24.856)转换为Real并返回，但我获得了以下值:

• 1362040-16036186-28 02:43:37
• 1362040-16036186-29 17:38:02

注意年份和月份是完全错误的，只有第二个值的时间是正确的。

我使用了以下代码，我正在阅读的文件是Safari的历史记录(只需访问Safari上的任何站点，记录系统时间并运行示例代码):

    Connection connection = null;
    try {
        String filename = "/Users/stefano/Library/Safari/History.db";
        connection = DriverManager.getConnection("jdbc:sqlite:" + filename);
        Statement statement = connection.createStatement();
        statement.setQueryTimeout(30);
        TreeSet<String> values = new TreeSet<>(reverseOrder());
        ResultSet results = statement.executeQuery(
            "SELECT datetime(visit_time) AS value FROM history_visits;");
        while (results.next()) {
            String value = results.getString("value");
            values.add(value);
        }
        System.out.println(values.first());
    } catch (SQLException e) {
        System.err.println(e.getMessage());
    } finally {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            System.err.println(e.getMessage());
        }
    }

问题是:你知道为什么会这样吗?我怎样才能得到期望的时间戳?提前感谢。

PS:请注意，如果我使用DB Browser for SQLite等应用程序访问文件，我会得到相同的数字，所以我认为问题在于编码本身……任何想法?

Answer 1

这里的问题是Safari显然没有以标准SQLite REAL日期时间格式存储visit_time值。相反，它以秒为单位存储相对于2001-01-01 00:00:00 UTC的值。有关详细信息，请参见

Safari History.db的格式是什么?visit_time ?