On Feb 22, 2:32 pm, Iain Barnett <[email protected]> wrote: > On 22 Feb 2012, at 19:41, Peter van Hardenberg wrote: > > > > > I'd also recommend using the timestamptz data type everywhere. > > Is it possible to specify this using the Sequel constants/datatypes? I > believe that DateTime and Sequel::CURRENT_TIMESTAMP are both "timestamp > without time zone". Would it be right to use this in a migration, for example? > > timestampz :created_at, null: false, default: Sequel::CURRENT_TIMESTAMP
That will work (well, it's timestamptz and not timestampz). Personally, I don't recommend using timestamptz unless your application actually deals with times in different zones. If all of your times are in the same zone, timestamptz provides no benefit in my opinion. > Sorry, I didn't make that clear. Initially I hadn't specified that setting at > all and only the migration specified the datatype with a default constraint > of Sequel::CURRENT_TIMESTAMP. It was when I found the problem that I did a > bit of research and found these > settingshttp://sequel.rubyforge.org/rdoc/classes/Sequel/Timezones.html, and > then applied them the way I said originally in the hope it would help. Well, one of the things I didn't think about earlier was that when you are using Sequel::CURRENT_TIMESTAMP, there is no application->database timestamp conversion. However, CURRENT_TIMESTAMP is always in localtime in PostgreSQL, which is probably why things were in local time even if your application had things set to UTC. Your original post said you used "Sequel.default_timezone = :utc", which as I explained, is the wrong setting if your database is storing stuff in localtime. With default_timezone = :utc, then database_timezone = :utc, but as CURRENT_TIMESTAMP uses localtime and you were using timestamp and not timestamp with time zone, you ended up with issues. pvh is correct that timestamptz might be better in your particular case, since it will convert the CURRENT_TIMESTAMP value to UTC internally (see http://www.postgresql.org/docs/9.1/static/datatype-datetime.html). > Without the setting, with my original setting, and with the suggested > setting, all took a time from the database that had been set by the default > constraint - a timestamp without time zone that somehow had timezone info - > and displayed it so it was 8hrs less than it should be. Whether that's > because the data had already had 8hrs taken off and Sequel ignored the > timezone info (that's my guess looking at the data) or it applied the > timezone info to a UTC time. > > As an example, I just created this record letting the default constraint set > the time. > > SQL shows: 2012-02-22 14:05:57 -0800 > Sequel console: 2012-02-22 14:05:57 UTC > > Settings: > Sequel.database_timezone = :local > Sequel.application_timezone = :utc > > I think Sequel is doing the right thing given the situation by ignoring the > timezone info, which is reassuring in one sense - it could've applied the > extra -8hrs! Actually, that's the wrong behavior. If the database is storing the data in local time, it should change it to the same absolute time in the new timezone. I can't replicate what you are seeing: irb(main):001:0> Sequel.database_timezone = :local => :local irb(main):002:0> Sequel.application_timezone = :utc => :utc irb(main):003:0> DB.create_table!(:as) {Time :a, :default=>Sequel::CURRENT_TIMESTAMP} => nil irb(main):004:0> DB[:as].insert => nil irb(main):005:0> DB[:as].all => [{:a=>Wed Feb 22 23:41:22 UTC 2012}] irb(main):006:0> Sequel.application_timezone = :local => :local irb(main):007:0> DB[:as].all => [{:a=>Wed Feb 22 15:41:22 -0800 2012}] I can replicate what you are seeing if database_timezone = :utc, though: irb(main):008:0> Sequel.database_timezone = :utc => :utc irb(main):009:0> DB[:as].all => [{:a=>Wed Feb 22 15:41:22 UTC 2012}] The algorithm Sequel uses to do the timezone conversion is moderately complex. In addition to checking the database_timezone and application_timezone settings, it also checks whether the timestamp string includes offset information. If the timestamp string includes offset information, Sequel assumes the offset information is correct. If the timestamp string does not include offset information (and PostgreSQL timestamp without time zone columns do not, e.g. "2012-02-22 15:45:47.46295"), Sequel assumes it is already in the database_timezone. You can read lib/sequel/timezones.rb for details. Jeremy -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sequel-talk?hl=en.
