Local timezone issues

Hi,

I've checked many of the related timezone questions in the forum but
none helped.

I'm on Rails 3.0.6 and using Postgresql.

I have my server time zone set to UTC with config.time_zone = 'UTC' in
my application.rb (it should be default anyway)

So I store Visit records in UTC: 2011-08-28 13:23:48.714506
I am in timezone +3 so I can verify that the stored created at is in
UTC.

Rails.logger.debug visits.last.created_at.class returns
ActiveSupport::TimeWithZone

I'm using pgAdmin3 and when checking the table on the UI app you can see
the datatypes. Strangely there in the UI of pgAdmin3 it reads
"created_at timestamp without time zone". That is odd.

Based in what I've read the data is in DB in UTC but when it pulls it
from the db it should convert it to local automatically.

I am in zone +3 so I should get 16:23:48 for the record above. But it
still comes in UTC.

Any hints what could be the problem?

Hi,

So I store Visit records in UTC: 2011-08-28 13:23:48.714506
I am in timezone +3 so I can verify that the stored created at is in
UTC.

Rails.logger.debug visits.last.created_at.class returns
ActiveSupport::TimeWithZone

I'm using pgAdmin3 and when checking the table on the UI app you can see
the datatypes. Strangely there in the UI of pgAdmin3 it reads
"created_at timestamp without time zone". That is odd.

Based in what I've read the data is in DB in UTC but when it pulls it
from the db it should convert it to local automatically.

I am in zone +3 so I should get 16:23:48 for the record above. But it
still comes in UTC.

Any hints what could be the problem?

It converts the utc value from utc to whatever Time.zone is. Unless
you're setting Time.zone elsewhere, because you've set
config.time_zone to utc, Time.zone will be utc too. Try setting
config.time_zone to the desired timezone (don't forget to restart your
app)

Fred

Hi,

So I store Visit records in UTC: 2011-08-28 13:23:48.714506

I am in timezone +3 so I can verify that the stored created at is in

UTC.

Rails.logger.debug visits.last.created_at.class returns

ActiveSupport::TimeWithZone

I’m using pgAdmin3 and when checking the table on the UI app you can see

the datatypes. Strangely there in the UI of pgAdmin3 it reads

“created_at timestamp without time zone”. That is odd.

Based in what I’ve read the data is in DB in UTC but when it pulls it

from the db it should convert it to local automatically.

I am in zone +3 so I should get 16:23:48 for the record above. But it

still comes in UTC.

When you say this, how are you determining that it ‘still comes in utc’? From what you see on a view?

Fred is right, for example on a certain app of mine the user can set their time zone so on the app controller I set ‘Time.zone = …’ in a before filter for the user. You could set this per user or app wide.

David K. wrote in post #1018917:

> ActiveSupport::TimeWithZone

When you say this, how are you determining that it 'still comes in utc'?
From what you see on a view?

Fred is right, for example on a certain app of mine the user can set
their
time zone so on the app controller I set 'Time.zone = ...' in a before
filter for the user. You could set this per user or app wide.

Thanks Fred and David.

I know it comes in utc because it is the same as stored in the DB. The
one I should see in the view should be that +3 hours.

I'm still confused by the wording in the documentation in
http://guides.rubyonrails.org/configuring.html

"config.active_record.default_timezone determines whether to use
Time.local (if set to :local) or Time.utc (if set to :utc) when pulling
dates and times from the database. The default is :utc for Rails,
although Active Record defaults to :local when used outside of Rails."

So if I set ActiveRecord::Base.default_timezone = :local then when
pulling the dates it should convert the UTC dates to Local. Right?

I don't know for sure how Rails knows the local time (browser?) because
I couldn't find anything in the request object that would indicate that.
When request something locally I can see: ...for 127.0.0.1 at 2011-08-28
20:53:49 +0300 for example.

In any case I have the part to get the timezone offset from the browser
and I could use that info in a before filter. But the way I was reading
the docs I thought I could save that before filter.

A bit mixed up...

David K. wrote in post #1018917:

> On Sun, Aug 28, 2011 at 8:09 AM, Frederick Cheung <
> frederick.che...@gmail.

I don't know for sure how Rails knows the local time (browser?) because
I couldn't find anything in the request object that would indicate that.
When request something locally I can see: ...for 127.0.0.1 at 2011-08-28
20:53:49 +0300 for example.

In any case I have the part to get the timezone offset from the browser
and I could use that info in a before filter. But the way I was reading
the docs I thought I could save that before filter.

Local would mean whatever the local timezone is set to on the server
(and influenced by things like the TZ environment variable. I seem to
remember that setting is best left as :utc, using config.time_zone
(which controls the default value of Time.zone) for making your times
display in whatever timezone you need.

Fred

Frederick Cheung wrote in post #1018934:

David K. wrote in post #1018917:

> On Sun, Aug 28, 2011 at 8:09 AM, Frederick Cheung <
> frederick.che...@gmail.

Local would mean whatever the local timezone is set to on the server
(and influenced by things like the TZ environment variable. I seem to
remember that setting is best left as :utc, using config.time_zone
(which controls the default value of Time.zone) for making your times
display in whatever timezone you need.

Fred

Yeah. I added a filter to set the time zone to whatever the user has.
I'm using https://bitbucket.org/pellepim/jstimezonedetect/wiki/Home

It seems to go ok and fetches the records with times converted to the
user time zone.

With a "normal" query it goes ok. Bit I had an issue with something
like:

@total_visits_by_day = Visit.where("tag_id = ?",
identifier).order("DATE(created_at)").group("DATE(created_at)").count

I couldn't find a way to feed in_time_zone to the created_at in the
query.

I solved that by first making a "normal" request and then grouping that:
visits = Visit.where(:tag_id => identifier).all
@total_visits_by_day = visits.group_by{|day|
day.created_at.strftime("%Y-%m-%d")}.map{|k,v| [k, v.length]}

So now I'm getting the dates converted to the user's time zone.

Frederick Cheung wrote in post #1018934:

With a "normal" query it goes ok. Bit I had an issue with something
like:

@total_visits_by_day = Visit.where("tag_id = ?",
identifier).order("DATE(created_at)").group("DATE(created_at)").count

I couldn't find a way to feed in_time_zone to the created_at in the
query.

That's because when mysql computes DATE(created_at) it knows nothing
about Rails' Time.zone.
Mysql has its own timezone conversion functions, so you can do stuff
like DATE(CONVERT_TZ(created_at, 'UTC', 'Europe/London') ) which
converts the time to the requested timezone before manipulating it

Fred