Extracting month/year from Time Column in sqlite3

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. Any suggestions will be helpful

Thanks in advance.

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 this - 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;")