SQLite JDBC rs.getDate() getTimestamp() 等都返回错误值

2022-01-13 00:00:00 datetime timestamp date sqlite jdbc

当出于某种原因使用 JDBC for SQLite 时，日期和时间戳值正确存储在数据库中，在使用命令行 sqlite3 工具时正确显示，但是当使用 ResultSet 函数检索这些值时，它不起作用.下面是一个小型测试类，演示了我的意思.

When using the JDBC for SQLite for some reason Date and Timestamp values are stored correctly in the DB, are displayed correctly when using the command line sqlite3 tool, but when using the ResultSet functions to retrieve these values it doesn't work. Below is a small test class that demonstrates what I mean. 

import java.sql.*;

public class Test {
  public static void main(String[] args) throws Exception {
    Class.forName("org.sqlite.JDBC");
    Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");
    Statement stat = conn.createStatement();
    stat.executeUpdate("drop table if exists people;");
    stat.executeUpdate("create table people (name, occupation, date date);");
stat.executeUpdate("insert into people values ('Turing', 'Computers', date('now'));");

    ResultSet rs = stat.executeQuery("select * from people;");
    while (rs.next()) {
      System.out.println("name = " + rs.getString("name"));
      System.out.println("job = " + rs.getString("occupation"));
      System.out.println("date = " + rs.getDate("date"));
      System.out.println("dateAsString = " + rs.getString("date"));
    }
    rs.close();
    conn.close();
  }
}

我得到的输出是:

name = 图灵
工作 = 计算机
日期 = 1970-01-01
dateAsString = 2011-03-24

name = Turing
job = Computers
date = 1970-01-01
dateAsString = 2011-03-24

推荐答案

就像 Scott 说的:SQLite 没有 Date 类型.

Like Scott says: SQLite does not have a Date type.

您可以让 SQLite 使用 strftime 函数为您进行转换:strftime('%s', 日期).然后就可以在Java端使用rs.getDate了.

You could have SQLite do the conversion for you with the strftime function: strftime('%s', date). Then you can use rs.getDate on the Java side.

您还可以将 SQLite 日期作为字符串检索，并将其解析为日期:

You can also retrieve the SQLite date as string, and parse that into a date:

DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
try {
    Date today = df.parse(rs.getString("date"));
    System.out.println("Today = " + df.format(today));
} catch (ParseException e) {
   e.printStackTrace();
}

相关文章