#791 hold

do_mysql inserts incorrect time if using time zone other than local

Reported by siplux | January 28th, 2009 @ 04:46 PM

In MySQL, datetime columns do not retain any time-zone information unlike for example, PostGreSQL which saves the TZ. MySQL instead assumes that the time is local and adjusts it to UTC before inserting (http://tinyurl.com/d9syqn). This will causes a problem if you're working exclusively with UTC timezones rather than local. MySQL, with no concept of TZ, will attempt to convert an a UTC timestamp to UTC which will obviously end up writing the wrong datetime.

Date(times) are always set to the current time zone while maintaining the date information in the database, this results in an incorrect time (UTC elements with a local time zone).

class Article
  property :created_at, Time


Article.create(:created_at => Time.now.utc - 1.hour)

Article.all(:created_at.lte => Time.now.utc) -> [#<Article id=1 created_at=Thu Jan 29 01:30:54 -0700 2009>]  

Article.all.find_all {|a| a.created_at <= Time.now.utc} -> [] 

Article.first.created_at < Time.now.utc -> false
(Time.utc *Article.first.created_at.to_a) < Time.now.utc -> true

Comments and changes to this ticket

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.

New-ticket Create new ticket

Create your profile

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