Discrepancy between System and Db times

Question

I have a rails application and I store all time in UTC in the database for TimeZone differences' purposes. I also expire a record instead of deleting it by setting "effective_end_date" field in the table to current time. Then I use named scope as follows in the model:

  named_scope :valid, :conditions => ['(effective_end_date IS NULL OR effective_end_date > ?) 
                                 AND (effective_start_date IS NULL OR effective_start_date < ?) ',Time.zone.now.gmtime, Time.zone.now.gmtime]

This seems to work fine on my Mac dev machine but once I move to production there seems to be discrepancy between the system time and the time which I'm not sure why!! Typing "date" command in Linux seems to give the right time. Looking at the production log file below:

sms parser(inparser) daemon is still running at Wed Jun 03 22:38:36 -0700 2009.
[4;35;1mUltrasmsin Load (0.5ms)ESC[0m   ESC[0mSELECT * FROM `smsin` WHERE ((effective_end_date IS NULL OR effective_end_date > '2009-06-04 05:28:32') 
 AND (effective_start_date IS NULL OR effective_start_date < '2009-06-04 05:28:32') )

This the generated query from the following lines of code:

ActiveRecord::Base.logger.info "sms parser(inparser) daemon is still running at #{Time.now}.\n"
nonConvertedMsgs = Ultrasmsin.valid.find(:all)

The first command time displayed from "Time.now" is correct but the second time (fetched from the named scope) seems to be wrong!! (off by 10 minutes)

This is really puzzling me as I would think Time.zone.now.gmtime would just convert hours and wouldn't touch the minutes but it seems that hours are converted ok to GM Time but the minutes are off by 10 minutes!

Any ideas?

Answer 1

On your Mac development machine, everything - DBMS, Rails, browser - is probably running in a single time zone, and it is your time zone.

On your production machine, it is likely that something is running in a different time zone. How a DBMS handles differences between client time zone and the database time zone varies, depending on the DBMS. Some operate in the DBMS's time zone - whatever time zone was set in its environment when it was started. Some take into account the client's time zone. Sometimes, there is no easy way to find the client's time zone.

In general, time zones in the modern world are multiples of 1 hour off UTC. There are exceptions - both India (+05:30) and Newfoundland (-04:30) are a multiple of half an hour off UTC, and Nepal is on (+05:45). However, a malformed time zone setting could throw things off.

Also remember that the clocks on the client and server may not be synchronized to an atomic clock somewhere, so a ten minute drift could be due to the lack of SNTP (NTP) service on the machine.