datetime - SQLite JDBC rs.getDate() getTimestamp() etc. all return wrong values

Question

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

The output I get is:

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

Answer 1

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

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.

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