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

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

Solution 2

SQLite does not use 'types' and instead uses what's known as 'type affinity';

From the documentation itself:

The important idea here is that the type is recommended, not required

You can store the dates as strings and parse them however you like with formatting classes like SimpleDateFormat or DateTimeFormatter, or you can use something crude like Date.from(Instant.parse(rs.getString("date"))).

Java 8

Amongst the other answers here, I'd say that LocalDate#parse() and LocalDateTime#parse() is also a good option for Java 8 moving forwards.

They accept a date in String format

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

Or a String with a DateTimeFormatter as the second argument

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

---

Conversion

And if you're looking to use LocalDate or LocalDateTime but want to support Date, maybe in an adapter pattern;

LocalDateTime to Date:

This example uses the system default timezone as the ZoneId argument of atZone, however you can use a static/hard-coded timezone if you want to. Essentially you are creating an Instant from AtZone which can be used to build a Date from the static method Date#from(Instant)

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

LocalDate To Date:

This example is pretty much the same, only here all that's required to get a ZoneId in order to create an Instant is the LocalDate instance itself.

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

---

I've not gone into technical detail here, it may not even be necessary, but do correct me if I'm wrong.

Solution 3

SQLite3 does not have a Date type so you will have to get the String of the dates when writing your code.

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