sqlite: dates and times are returned as strings

[dgmora]

sqlite does not have a different storage class for dates and times. Because of this, blazer can't identify the field as a timestamp and ends up using a different type of chart (a barchart instead of a line chart). This happens here, the value is returned (unexpectedly) as a String:

blazer/lib/blazer/result.rb

Lines 48 to 67 in 134a969

	def column_types
	@column_types ||= begin
	columns.each_with_index.map do |k, i|
	v = (rows.find { |r| r[i] } || {})[i]
	if smart_values[k]
	"string"
	elsif v.is_a?(Numeric)
	"numeric"
	elsif v.is_a?(Time) || v.is_a?(Date)
	"time"
	elsif v.nil?
	nil
	elsif v.is_a?(String) && v.encoding == Encoding::BINARY
	"binary"
	else
	"string"
	end
	end
	end
	end

I'm not quite sure what would be a good solution here. Trying to parse every string is odd. The schema information isn't reliable, since you can also add new columns that aren't part of the schema. Maybe it could be a configurable option like

sqlite:
  column_types:
    - created_at: "time"
    - my_date: "time"

Or there could be certain suffixes like _date / _timestamp / _at that are treated as time if it's sqlite and the value is a string? (configurable as well, maybe)

[ankane]

Hi @dgmora, thanks for reporting. I think the best way to address this would be to get the types from the raw result and cast based on that.

connection_model.connection.raw_connection.query(sql, binds) do |result|
  p result.types
end

[dgmora]

Interesting! The types not available with connection_model.connection.select_all though. So that would require to use the the raw_connection and manually build a result so that it contains the types,right?

[ankane]

Correct, and the approach could ideally be upstreamed to Active Record / select_all in the future (possibly here).