Sequel with Sqlite caveat: Sorting on dates 2008-03-20

One slight hickup it's worth being aware of when storing timestamps in Sequel is that if you use the Sqlite adapter, the timestamp is stored as an ISO 8601 string with timezone, like this:

2008-03-20T11:26:52Z

The "Z" at the end signifies UTC.

This isn't a problem if you're consistent, but if you parse times with Time.parse() in Ruby, and some of the strings you parse contain another timezone, you might end up getting times in different timezones in your database.

Needless to say that screws up sorting.

Avoiding this is pretty simple, though the best solution would probably be to fix the Sequel Sqlite adapter - I'll take a look at that later. Here's my quick workaround, which also illustrates Sequel hooks:

class Item < Sequel::Model
  before_save do
      self.published_at = Time.parse(self.published_at.to_s).utc
    end
  end
end

Anything in the "before_save" block gets executed before an insert or update. Similarly you can add hooks after insert/update with "after_save". In this case all I do is convert whatever is in published_at to a string and reparsing it and forcing it to UTC. Which timezone you force it too doesn't matter, as long as you're consistent it will sort correctly.

(As a side effect, the code above also happily lets me do things like Item.published_at = :now to set the timestamp automatically on save)