Just realized I have a nice problem. using Postgresql... When I save something via Rails to the table it''s saved with a GMT offset (so 12:00 becomes 16:00) But the database is configured to save everything as GMT. Which means -- when I query it via SQL it''s coming back as now + 4 hours instead of just plain now. Where/How do I get this back in sync? -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
> Just realized I have a nice problem. > > using Postgresql... > > When I save something via Rails to the table it''s saved with a GMT > offset (so 12:00 becomes 16:00) > But the database is configured to save everything as GMT. > > Which means -- when I query it via SQL it''s coming back as now + 4 > hours instead of just plain now. > > Where/How do I get this back in sync?Directly via SQL... you can''t since that''s what the values are. However, through AR you can... Not sure if it''s moved in 3.1, but in 3.0, the following is in config/application.rb # Set Time.zone default to the specified zone and make Active Record auto-convert to this zone. # Run "rake -D time" for a list of tasks for finding time zone names. Default is UTC. config.time_zone = ''Pacific Time (US & Canada)'' -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
OK, so the default time saved is UTF-8. Which means that storing it a what appears to be now+4 hours is correct. I think my question falls into a postgresql question. When I query these fields it appears as the time (16:00 instead of 12:00) is identified as "timestamp with time zone" -- unexpected. I don''t think I quite understand what it is I am looking at... On Tue, Oct 25, 2011 at 12:59 PM, Philip Hallstrom <philip-LSG90OXdqQE@public.gmane.org> wrote:>> Just realized I have a nice problem. >> >> using Postgresql... >> >> When I save something via Rails to the table it''s saved with a GMT >> offset (so 12:00 becomes 16:00) >> But the database is configured to save everything as GMT. >> >> Which means -- when I query it via SQL it''s coming back as now + 4 >> hours instead of just plain now. >> >> Where/How do I get this back in sync? > > Directly via SQL... you can''t since that''s what the values are. However, through AR you can... > > Not sure if it''s moved in 3.1, but in 3.0, the following is in config/application.rb > > # Set Time.zone default to the specified zone and make Active Record auto-convert to this zone. > # Run "rake -D time" for a list of tasks for finding time zone names. Default is UTC. > config.time_zone = ''Pacific Time (US & Canada)'' > > -- > You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. > To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. > For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en. > >-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On Oct 25, 2011, at 11:50 AM, Tom Allison wrote:> timestamp with time zoneWhich means that it is offset by postgres to/from the time zone of the client making the query, your rails app in this case. Timestamp without time zone makes no adjustments, just storing/retrieving it as supplied. -- Scott Ribe scott_ribe-ZCQMRMivIIdUL8GK/JU1Wg@public.gmane.org http://www.elevated-dev.com/ (303) 722-0567 voice -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On Tue, Oct 25, 2011 at 5:52 PM, Tom Allison <tom-dPKA337BxZXR7s880joybQ@public.gmane.org> wrote:> Just realized I have a nice problem. > > using Postgresql... > > When I save something via Rails to the table it''s saved with a GMT > offset (so 12:00 becomes 16:00) > But the database is configured to save everything as GMT. > > Which means -- when I query it via SQL it''s coming back as now + 4 > hours instead of just plain now. > > Where/How do I get this back in sync?In SQL Standard you set a client timezone using SET TIME ZONE, which PostgreSQL supports. Timezone can be set using the "timezone" configuration parameter, which can be set in the file postgresql.conf, or in any of the other standard ways described, such as set for each session using SET. There are also some special ways to set it: * The SQL command SET TIME ZONE sets the time zone for the session. * The PGTZ environment variable is used by libpq clients to send a SET TIME ZONE command to the server upon connection. So you can set this on the client or the server, for the user, explicitly and other ways. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Thank you very much. I had a feeling it was a postgres thing and not a rails thing. I''m impressed at what rails/ruby can do about timezone just the same. Personally, I find working with date/time to be the most problematic data type *ever*! On Wed, Oct 26, 2011 at 4:16 AM, Simon Riggs <simon-lJk9oCk3lhPBpgsEml150g@public.gmane.org> wrote:> On Tue, Oct 25, 2011 at 5:52 PM, Tom Allison <tom-dPKA337BxZXR7s880joybQ@public.gmane.org> wrote: >> Just realized I have a nice problem. >> >> using Postgresql... >> >> When I save something via Rails to the table it''s saved with a GMT >> offset (so 12:00 becomes 16:00) >> But the database is configured to save everything as GMT. >> >> Which means -- when I query it via SQL it''s coming back as now + 4 >> hours instead of just plain now. >> >> Where/How do I get this back in sync? > > In SQL Standard you set a client timezone using SET TIME ZONE, which > PostgreSQL supports. > > Timezone can be set using the "timezone" configuration parameter, > which can be set in the file postgresql.conf, or in any of the other > standard ways described, such as set for each session using SET. There > are also some special ways to set it: > > * The SQL command SET TIME ZONE sets the time zone for the session. > > * The PGTZ environment variable is used by libpq clients to send a > SET TIME ZONE command to the server upon connection. > > So you can set this on the client or the server, for the user, > explicitly and other ways. > > -- > Simon Riggs http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services > > -- > You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. > To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. > For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en. > >-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
UTC for everything, always. Localize the time in rails on the front- end, but always store and compare in UTC on the back-end. Trust me, it will make your life a lot easier. On Oct 25, 9:52 am, Tom Allison <t...-dPKA337BxZXR7s880joybQ@public.gmane.org> wrote:> Just realized I have a nice problem. > > using Postgresql... > > When I save something via Rails to the table it''s saved with a GMT > offset (so 12:00 becomes 16:00) > But the database is configured to save everything as GMT. > > Which means -- when I query it via SQL it''s coming back as now + 4 > hours instead of just plain now. > > Where/How do I get this back in sync?-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.