Geek Answers Handbook

SQLite JDBC rs.getDate () getTimestamp () etc. All return incorrect values

When using JDBC for SQLite for some reason, the date and time values ​​are stored correctly in the database, they are displayed correctly when using the sqlite3 command-line tool, but when using the ResultSet functions to extract these values, it does not 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();
  }
}

The output I get is:

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

+5
source share
3 answers

: SQLite Date.

SQLite strftime: strftime('%s', date). rs.getDate Java.

SQLite :

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

SQLite3 Date, .

http://www.sqlite.org/datatype3.html

+2

SQLite "" , " ";

:

, ,

, , , SimpleDateFormat DateTimeFormatter, - , Date.from(Instant.parse(rs.getString("date"))).

Java 8

, LocalDate#parse() LocalDateTime#parse() Java 8 .

String

LocalDate.parse("2016-05-24")

DateTimeFormatter

LocalDate.parse("2016-05-24", DateTimeFormatter.ofPattern(yyyy-MM-dd))

LocalDate LocalDateTime, Date, , ;

LocalDateTime to Date:

ZoneId atZone, / , . , Instant AtZone, Date#from(Instant)

Date.from(localDate.atStartOfDay().atZone(ZoneId.systemDefault()).toInstant()));

LocalDate To Date:

, , ZoneId Instant, - LocalDate.

Date.from(localDate.atZone(ZoneId.systemDefault()).toInstant()));

, , , .

+2

More articles:


All Articles