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