Accessing SQLite3 DATETIME column data with the Sequel gem “jdbc” SQLite adapter produces different date types for the DATETIME columns than does the MRI Sequel adapter. So, you get ‘Time’ objects in the result sets when using standard Ruby, but ‘Date’ types when using JRuby. The ‘Date’ objects don’t have a time component; the ‘Time’ objects have both date and time.
ActiveRecord with the ‘ ‘activerecord-jdbcsqlite3-adapter’ yields records with a ‘Time’ type field from the SQLite DATETIME columns; the problem lies in the Sequel JDBC adapter.
The Problem
Aside from the inconsistency between MRI Ruby and JRuby, the more serious problem is that the ‘Date’ type returned in the JRuby version loses the time component of the DATETIME column.
As a consumer of the Sequel gem, solving the problem requires either changing the table schema, or changing the select statement used to get data from the SQLite database.
In our particular case we had some tables with a “file_timestamp” column with “str” type and some with “datetime” type, so we had to make a change somehow, to at least make our schema the same across the whole database.
Solution 1: Change the select statements, (Without changing the schema)
With Sequel JDBC you have to call the SQLite datetime() function in the selection to prevent the driver from wrongly assigning the ‘Date’ type to the results.
irb(main):066:0> results = db.fetch("select datetime(file_timestamp) as file_timestamp, variable from variable_trans_tables where variable='AGE'")
=> #<Sequel::JDBC::Dataset: "select datetime(file_timestamp) as file_timestamp, variable from variable_trans_tables where variable='AGE'">
irb(main):067:0> results.first
=> {:file_timestamp=>"2020-09-15 12:17:06", :variable=>"AGE"}
irb(main):068:0>
Solution 2:
Change the column type in the SQLite database file to TIMESTAMP, no changes to select needed
Then the JDBC sqlite driver keeps the exact value of the date-time string, while your database retains an actually meaningful column type.
Conclusion
Either way, you still have different types in your result sets depending on if you’re running MRI Ruby or Jruby. You can make the code consuming the DATETIME or TIMESTAMP column data work the same on either platform by converting values coming from the problematic columns to string everywhere, or converting to ‘Time’ instances everywhere if they aren’t already ‘Time’ instances.
In the MRI Ruby Sqlite adapter both TIMESTAMP and DATETIME SQLite column types get returned as Time types. You can do comparisons on them and do “.to_s”.
From JRuby, you can manually create a Time instance from these timestamp values (returned as ‘String’ by Sequel’s JDBC adapter.) In Rails projects, use Time.parse(time_string) with ActiveSupport from Rails:
results[:file_timestamp].to_time(:iso8601)
Details
I’ll retrieve the DATETIME data and check it’s type and convert it to strings. I’ll also show how a column with the TIMESTAMP type behaves differently from DATETIME.
Here is how both MRI Ruby and JRuby versions of the same operations go. The content in these columns is a string of a date-time in ISO 8601 format, not an integer representing the epoch. This could be considered a problem and is another thing to follow up on.
You establish a “connection” (SQLite is just a file) with Sequel.sqlite(filename) on MRI Ruby. On JRuby you have to use a JDBC adapter to allow Sequel to access a SQLite file: Sequel.jdbc("sqlite:/databasefile") .
With MRI and SQLite driver:
rb(main):016:0> tr = db[:variable_trans_tables].where(variable: "AGE").first
=> {:variable=>"AGE", :sample=>"integrated_variables", :is_svar=>false, :xml=>#<Sequel::SQL::Blob:0x3fffe5be54f4 bytes=4197674 start="<translati" end="n_table>\n\n">, :date_ created=>2020-07-16 13:22:02 -0500, :file_timestamp=>2020-07-16 13:22:02 -0500}
irb(main):020:0> tr[:file_timestamp].class
=> Time
irb(main):022:0> tr[:file_timestamp].to_s
=> "2020-07-16 13:22:02 -0500"
irb(main):024:0> tr[:date_created]
=> 2020-07-16 13:22:02 -0500
irb(main):025:0> tr[:date_created].class
=> Time
Using JRuby and JDBC
irb(main):001:0> require 'jdbc/sqlite3'
=> true
irb(main):002:0> require 'sequel'
=> true
irb(main):003:0> db=Sequel.jdbc("sqlite:/mnt/c/Users/ccd/metadata_dev.db")
=> #<Sequel::JDBC::Database: "jdbc:sqlite:/mnt/c/Users/ccd/metadata_dev.db" {:adapter=>:jdbc, :database=>"sqlite:/mnt/c/Users/ccd/metadata_dev.db"}>
irb(main):004:0> tr = db[:variable_trans_tables].where(variable: "WRKAVAIL").first
irb(main):005:0> tr[:file_timestamp]
=> #<Date: 2020-09-15 ((2459108j,0s,0n),+0s,2299161j)>
irb(main):006:0>
irb(main):007:0> tr[:file_timestamp].class
=> Date
irb(main):008:0> tr[:file_timestamp].to_s
=> "2020-09-15"
irb(main):010:0> tr[:date_created]
=> "2020-09-15 12:17:33.177"
irb(main):011:0> tr[:date_created].class
=> String