Some timezone trouble involving tzinfo and postgresql


So there I was, minding my own business, trying to make my rails app
timezone aware (DST, as well), since it’s all about the timing with
this one. There are a number of excellent articles that I’ve been
working off of, in particular this one internet archived version of
Scott B.'s very useful article on the subject that is no longer
available at the original URL that is linked to from the TZInfo page:

I’m not looking to pull of anything fancy here, but I’ve ended up a
bit of a stumper in which utc_to_local appears to be adjusting the UTC-
stored time twice.

This is what I’ve done so far:

  1. Set ENV[‘TZ’] = ‘UTC’ in config/environment.rb

  2. Uncommented this line in config/environment.rb:
    config.active_record.default_timezone = :utc

  3. Installed the tzinfo gem.

  4. Added a require statement for tzinfo into config/environment.rb:
    require ‘tzinfo’

  5. Added a timezone selector to my user account profiles, and a string
    field in the database to support it. Now this topic deserves a
    diversion, as the use of an active record aggregation has been nothing
    but problems, and treating it as a string has been the only useful
    means of doing this. Here’s what I mean:

We want to be able to access the user’s timezone attribute as a
TZInfo::Timezone object instance so that we can say things like:

But whenever I used the standard aggregation shown in examples on this
topic, the drop down time_zone_select for TZInfo doesn’t recognize the
user’s previous selection! So I got around that like thus:

i. created a string field on the user model called time_zone
ii. added an accessor tz that will give us the object support
def tz
iii. and this is my select that can reproduce the previous selection:

<%= f.time_zone_select :time_zone, TZInfo::Timezone.all.sort, :model => TZInfo::Timezone %>

When I used the composed_of method to try and handle the time_zone
field without any other methods, the time_zone_select method kept
letting me down, not setting the previously selected value in the DDL.

Okay, diversion over. Moving on:

  1. So let’s just say my app allows users to make many foos. One at a
    time. And each foo goes to the bar for a pint at a specific time, but
    we initially set it to, usually. Well, now I’ve updated
    foo_controller to do it according to the user’s time_zone:

def new
@foo =
@foo.goes_to_bar =

This correctly sets the selector for the timing of the Foo to “now” in
the user’s local time.

On save, this value is appropriately stored in postgresql in UTC.

  1. Now, it’s time display the Foo. I don’t want to just print out
    the value in UTC, so I’ll use utc_to_local to show it off:

<%= %>

Eventually, I’ll make a helper for this. But let’s just work with
this example for now.

Right here is where I run into a problem (thank you for sticking
around this long!) When I print Foo’s datetime column, correctly
stored in UTC, I find that the resulting output appears to have been
twice calculated to local time. Thus, if it was stored at 1400 EDT,
instead of coming back 1400 EDT, it’s coming back 1000 UTC!

And to boot, when it gets printed, by default it’s getting shown with
this incorrect TZ (which I’ll eliminate later via sprintf, but still
it doesn’t bode well):

“Mon Apr 02 10:25:00 UTC 2007”

That’s not so great. Can anyone here help me shed some light on
this? I don’t think I’m doing anything particularly fancy here. My
initial thoughts are that:

a. perhaps I’m being redundant in environment.rb by setting UTC in two
b. perhaps I need to set postgres’s timezone to UTC (this would be
annoying to do to other non-tz aware apps)
c. perhaps those “timestamp without time zone” columns that rails
generates for datetime columns need to support the time zone? this
doesn’t make much sense, since all things are agreeing to store time
in UTC. it might also be bad in general since that’s rather db-vendor-

Thanks so much for any help! I think that this is a pretty common
journey folks are trying to make with Rails apps and the various
articles out there don’t seem to arrive at a full and working solution
(as they are building on eachother’s work, have old code, etc). I’d
love to give Jamis B.'s tztime a shot, but I feel like I need to
grasp the basics of this first.

All my best,

Also, and this is worth noting, that when I print out the UTC-stored
date without the use of a utc_to_local helper, it is being printed in
the original timezone. It’s as though active_record were keeping
track of the initial tz offset when the object was stored. Thus, to
summarize, a date entered in EDT 1400 is being correctly stored at
1800 UTC (conversion is being done by active record, local_to_utc was
not used), when printed without the utc_to_local helper, it appears
active record has already converted the datetime back to 1400 EDT (but
still prints the string UTC), and when I print it with the helper
utc_to_local, I am adjusting it a second time to 1000 EDT.