Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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

like image 518
Omar Kohl Avatar asked Mar 24 '11 21:03

Omar Kohl


3 Answers

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();
}
like image 86
Martijn Avatar answered Sep 29 '22 09:09

Martijn


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

like image 41
Scott Nguyen Avatar answered Sep 29 '22 08:09

Scott Nguyen


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.

like image 39
cossacksman Avatar answered Sep 29 '22 09:09

cossacksman