How to extract the month/year from Time column in SQLite3 using Sequel

Question

I have a table like this:

db = Sequel.sqlite
db.create_table? :timesheets do
    primary_key :id
    String      :project
    Time        :start
end

where I insert a record like so:

db[:timesheets].insert(:project => 'AAA', :start  => Time.now)

How would I extract the "year" from the entry? I tried the following:

db[:timesheets].select(:strftime.sql_function('%Y', :start)).filter(:id => 1).first
db[:timesheets].select(Sequel.extract(:month, :start)).filter(:id => 1).first
db.fetch("SELECT strftime('%Y', start) FROM timesheets;").first

but none of them seemed to work.

Answer 1

Ok. I finally figured it out after many days of head banging.

Time.now does return the Time value in the standardized date format that SQLite can take. However, if the Time Zone information is encoded in it, it becomes a non-standard format.

That is, if Time.now gives you YYYY-MM-DD HH:MM:SS.SSSSS, then you're okay:

2012-09-12 16:34:31.331394

But if it gives you something like YYYY-MM-DD HH:MM:SS.SSSSS +TimeZone:

2012-09-12 16:34:31.331394+0530

then you're in trouble.

In case you need to encode the TimeZone also, you need to convert it:

Time.now.xmlschema

which will give you something like:

2012-09-12T16:40:45+05:30

which SQLite can then parse intelligently.

Hence, the INSERT statement in the question becomes:

db[:timesheets].insert(:project => 'AAA', :start  => Time.now.xmlschema)

Now, the following queries work fine:

db[:timesheets].select(:strftime.sql_function('%Y', :start)).filter(:id => 2)
db[:timesheets].select(:start.extract(:month)).filter(:id => 2)
db[:timesheets].fetch("SELECT strftime('%Y', start) FROM timesheets;")