This project is archived and is in readonly mode.
[patch] mysql time_zone should reflect default_timezone
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
-
mysql_connection_timezone.diff
1.12 KB
-
Geoff Buesing- 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- 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
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
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+1
I am having problems with this exact issue, that Mysql and Rails are set to different timezones. This patch looks useful.
-
Geoff Buesing
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- 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.