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 On 22 Feb 2012, at 19:37, Jeremy Evans wrote: > On Feb 22, 11:08 am, Iain Barnett <[email protected]> wrote: >> On 22 February 2012 18:13, Jeremy Evans <[email protected]> wrote: >> >>> On Feb 22, 8:34 am, Iain Barnett <[email protected]> wrote: >>>> Before the database is initialised: >> >>>> Sequel.default_timezone = :utc >> >>> This may be the problem. If the database is storing times in >>> localtime, then you shouldn't set default_timezone = :utc. You >>> probably want database_timezone = :local and application_timezone >>> = :utc. This will convert the times from UTC->Local before they go >>> into the database, and Local->UTC upon retrieval from the database. >> >> I tried this and it didn't work, unfortunately. I only added those settings >> after noticing the problem, maybe it wouldn't be an issue if they'd been >> there from the off. > > That is likely. Since you initially had set database_timezone = :utc, > the times that were in the database before you switched to > database_timezone = :local were in UTC and not local time, and would > need to be adjusted manually. > 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 settings http://sequel.rubyforge.org/rdoc/classes/Sequel/Timezones.html, and then applied them the way I said originally in the hope it would help. 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! Regards, Iain -- 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.
