Ruby on Rails | Screencasts | Download | Documentation | Weblog | Community | Source

Ticket #5517 (closed defect: untested)

Opened 2 years ago

Last modified 2 years ago

[PATCH] Time quoting does not take Base.default_timezone in to account

Reported by: alex@msgpad.com Assigned to: jamis@37signals.com
Priority: normal Milestone: 1.x
Component: ActiveRecord Version: edge
Severity: normal Keywords: date time datetime timestamp timezone needy
Cc:

Description

Summary: Queries using a Time object do not take in to account the Base.default_timezone.

A Ruby Time object represents time independently of it's internal storage. This leads to each of the following evaluating to true: 

time = Time.utc(2000,1,1,15,28)
time.gmt?
time.to_i == time.getlocal.to_i
time.to_s != time.getlocal.to_s

activerecord currently uses the Time.strftime method to quote date/time's. The problem comes when you store times in the database in one zone, say UTC, but construct an SQL query with a time object in the other zone (local). The problem is that strftime simply plucks out the hours, mins, etc components from the Time object, irrespective of the databases timezone. In such a case, the hours, mins, etc components will not correspond to the database, despite the actual time (seconds since epoch/to_i) being logically equivalent.

To rectify this problem, Time objects need to be translated to the applications Base.default_timezone before being access with strftime.

Please find attached a patch for the postgresql adapter and the base quoting module, as well as a test case.

Tested with mysql and postgresql before submitting.

Attachments

different_timezone_queries.diff (1.7 kB) - added by alex@msgpad.com on 06/27/06 02:56:25.
Patch to handle case when Time object timezone != DB's timezone.
different_timezone_queries_2.diff (1.7 kB) - added by alex@msgpad.com on 06/27/06 03:48:30.
fixed up copy+paste error found in original patch
pg_my_utc_default.patch (1.3 kB) - added by gerardo on 09/15/06 05:41:49.
Tell PostgreSQL and MySQL to use UTC as the default time zone when Rails is doing so.

Change History

06/27/06 02:56:25 changed by alex@msgpad.com

  • attachment different_timezone_queries.diff added.

Patch to handle case when Time object timezone != DB's timezone.

06/27/06 03:47:21 changed by alex@msgpad.com

oops, there was a copy and paste issue with quoting.rb where I left the microsecond portion in the format string. It's out now in the second attached diff.

06/27/06 03:48:30 changed by alex@msgpad.com

  • attachment different_timezone_queries_2.diff added.

fixed up copy+paste error found in original patch

07/05/06 01:22:51 changed by david

  • owner changed from David to jamis@37signals.com.

07/05/06 01:33:10 changed by minam

  • status changed from new to closed.
  • resolution set to wontfix.

Alex, thanks very much for taking the time to provide a patch for this behavior. However, David and I have discussed this and we really feel it is up to the programmer to covert the times before storing them in the database. Primarily, this is because of Ruby's lousy time-zone support. Consider the case where you are using the TZInfo lib to do time-zone conversion. In that case, "local" means different things. Do we mean system "local", or user "local"? If user local, the time will actually be a UTC value with the hour/minute/second for the user's local time zone, and there's not much we can do in AR to handle that, since (to AR), it looks just like a UTC value. You'd have to do that conversion yourself before submitting the query.

At any rate, time-zone support in Rails is a sticky issue at present, and will remain so until Ruby has better support for time-zones built into it.

If you find yourself doing system-local to UTC conversions frequently before submitting a query, please do consider making this functionality a plugin. Ruby, unfortunately, is not quite ready for something like this to be incorporated into Rails.

09/15/06 05:39:05 changed by gerardo

  • status changed from closed to reopened.
  • resolution deleted.

I found myself looking at local times with a UTC timezone just stamped of them (not nice).

Solved this on mysql (when supported, >= 4.1.3, see http://dev.mysql.com/doc/refman/4.1/en/time-zone-support.html, patch does NOT check on versions), and postgresql by telling the DB what the session TZ is, at (re)connection time, and the DB does the conversions. Patch attached.

Sorry, I'm not sure how to write a test case checking this.

In Postgresql, the column type of the relevant column(s) should be TIMESTAMP WITH TIME ZONE. Not sure in Mysql, looks like it works with TIMESTAMP.

From what I read (see http://www.dbasupport.com/oracle/ora9i/TimeZone.shtml), Oracle allows something similar, using "alter session set TIME_ZONE='UTC';" at (re)connection time, and the TIMESTAMP WITH LOCAL TIME ZONE type.

SQLite is a special case, UTC is the default, localtime a modifier (in each query).

Don't know the other DBs.

09/15/06 05:41:49 changed by gerardo

  • attachment pg_my_utc_default.patch added.

Tell PostgreSQL and MySQL to use UTC as the default time zone when Rails is doing so.

09/15/06 07:13:49 changed by bitsweat

  • keywords changed from date time datetime timestamp timezone to date time datetime timestamp timezone needy.
  • status changed from reopened to closed.
  • version set to edge.
  • resolution set to untested.
  • milestone set to 1.x.