Rails Ruby on Rails → 2.x

Comments and changes to this ticket

• 

  Geoff Buesing February 14th, 2009 @ 05:53 PM

  • State changed from “new” to “wontfix”

  ActiveRecord's default_timezone setting indicates whether your database is storing timestamps in UTC or the system local zone. It does not indicate, nor is it meant to change, what your system's local zone is set to.

  Given those requirements, "SELECT now()" should always return the current time in the system local zone, irrespective of ActiveRecord's default_timezone.

  If you need the current UTC time in MySQL, you can always use "SELECT UTC_TIMESTAMP()".

• You flagged this item as spam.
  

  Gaspard Bucher February 15th, 2009 @ 09:29 AM

  • Assigned user set to “Geoff Buesing”

  OK. Then we should not use "default_timezone" to configure the connection (I thought it acted like ENV['TZ']).

  Here is another patch that enables the use of an optional 'timezone' configuration:

  
  development:
    adapter: mysql
    database: foo_dev
    timezone: "+0:0"
    ...
  

  • mysql_timezone_configuration_option.diff 1.8 KB
• 

  Geoff Buesing February 15th, 2009 @ 05:43 PM

  What's a good example use case where this would come in handy? In your example above, you're setting the connection time zone to "+0:0", i.e., UTC, which means you'd be able to use "SELECT NOW()" and have it behave the same as "SELECT UTC_TIMESTAMP()". Beyond that, what useful behavior or functionality do you get by setting this?

  In the department of time zone config settings, Rails already has config.time_zone, config.active_record.default_timezone, and Time.zone. On top of that, you can set ENV['TZ'] for the process if you need to. If we're going to justify adding another time zone config setting into the mix, we'll need some clear-win use cases for it.

• You flagged this item as spam.
  

  Gaspard Bucher February 16th, 2009 @ 08:11 AM

  I understand that no one really wants to dive in the timezone thing once more, but I had a closer look on all those settings and I think my first patch really makes sense, even if the argument using Time.now was ill posed:

  Base.default_timezone's role is to make sure the created_at/updated_at are set to the current time during create/update and to instantiate time attributes. In fact this setting could be removed for a config:... in the database.yml but it wouldn't work because there is no consistent way to timestamp records between databases.

  Your idea of using utc_timestamp() instead of now is a half solution because it won't work if someone wants to use the application with local times (maybe he/she is sharing some elements of the db).

  Making sure 'default_timezone' is in sync with the database's TIME_ZONE is really the way to go. There is no reason to have different values for mysql TIME_ZONE and 'default_timezone'.

• You flagged this item as spam.
  

  Zac Zheng March 30th, 2009 @ 12:21 PM

  +1

  I am having problems with this exact issue, that Mysql and Rails are set to different timezones. This patch looks useful.

• 

  Geoff Buesing March 30th, 2009 @ 02:47 PM

  It's still not clear to me what this would allow you to do that you can't do now. Can you give some specific use cases, e.g., "I'm trying to do X but I can't"?

• You flagged this item as spam.
  

  gzminiz September 12th, 2010 @ 10:21 AM

  • Importance changed from “” to “”

  I have two external DB (that i don't have control of) that are saving records in different timezones to each other and my own database. So times aren't in sync, having a configuration option to set a TZ for each db would correct this.