This project is archived and is in readonly mode.

#1964 ✓wontfix
Gaspard Bucher

[patch] mysql time_zone should reflect default_timezone

Reported by Gaspard Bucher | February 13th, 2009 @ 10:34 PM | in 2.x

It should be possible to use "now()" in find conditions and the value should be coherent with Time.now.

The patch ensures that


Base.connection.execute("SELECT (now() - #{Time.now.strftime('%Y%m%d%H%M%S')})").fetch_row[0].to_f == 0.0

Comments and changes to this ticket

  • Geoff Buesing

    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()".

  • Gaspard Bucher

    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"
      ...
    
  • Geoff Buesing

    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.

  • Gaspard Bucher

    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'.

  • Zac Zheng

    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

    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"?

  • gzminiz

    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.

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>

People watching this ticket

Pages