Arel and pgAdmin return different data from same query with condition on timestamp

Question

This is the most puzzling problem I ran into with PostgreSQL 9.1 and Rails 3.0.7. I am running Postgres and Rails 3 on the same Ubuntu 10.04 vbox guest:

"PostgreSQL 9.1.3 on i686-pc-linux-gnu, compiled by gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 32-bit"

May app needs to run a simple query based on created_at timestamp falls on a single day based on server's timezone. In my case, Eastern Time. I have tried different variation of "with time zone" and "at time zone" and got it working fine in pgAdmin, but not in Rails web site.

Here is the class method with ARel query.

def self.transactions2(business_id, begin_date, end_date )
trans = LogVouchers.select( %{
      created_at
  } ).
where( %{
     created_at >= ( TIMESTAMP WITH TIME ZONE ? at time zone 'utc')::timestamp
    and created_at < (TIMESTAMP WITH TIME ZONE ? at time zone 'utc')::timestamp
  },
     begin_date,
    end_date  )

end

Here is the controller code:

  def voucher_transactions
    default_date = Time.zone.now.beginning_of_day

    @end_date = params[:end_date].blank? ? \
      Date.new(default_date.year, default_date.month,  default_date.day )  : \
        Date.new(params[:end_date][:year].to_i, params[:end_date][:month].to_i, \
        params[:end_date][:day].to_i)

    @begin_date = params[:begin_date].blank? ? \
      Date.new(default_date.year, default_date.month,  default_date.day )   : \
        @begin_date = Date.new(params[:begin_date][:year].to_i, \
        params[:begin_date][:month].to_i, params[:begin_date][:day].to_i)

    @data2  = LogVouchers.transactions2(business_id, @begin_date, @end_date + 1.day)
    ...
    end

Here is the actual SQL code and debug inspection in the rails log file:

      LogVouchers Load (0.7ms)  SELECT 
 created_at
 FROM "log_vouchers" WHERE (
 created_at >= ( TIMESTAMP WITH TIME ZONE '2012-04-28' at time zone 'utc')::timestamp
 and created_at < (TIMESTAMP WITH TIME ZONE '2012-04-29' at time zone 'utc')::timestamp
 )

  ### 2012-05-01 21:18:04 -0400  voucher_transactions() @data2 =[
  #<LogVouchers created_at: "2012-04-28 03:14:29">
, #<LogVouchers created_at: "2012-04-28 03:15:24">
, #<LogVouchers created_at: "2012-04-28 03:18:19">
, #<LogVouchers created_at: "2012-04-28 03:38:35">
, #<LogVouchers created_at: "2012-04-28 03:58:08">
, #<LogVouchers created_at: "2012-04-28 15:44:46">
, #<LogVouchers created_at: "2012-04-28 17:12:20">
, #<LogVouchers created_at: "2012-04-28 18:46:45">
, #<LogVouchers created_at: "2012-04-28 18:55:47">
, #<LogVouchers created_at: "2012-04-28 18:57:52">
, #<LogVouchers created_at: "2012-04-28 19:02:15">
, #<LogVouchers created_at: "2012-04-28 19:02:50">
, #<LogVouchers created_at: "2012-04-28 19:46:36">
, #<LogVouchers created_at: "2012-04-28 19:47:17">
, #<LogVouchers created_at: "2012-04-28 19:50:22">
, #<LogVouchers created_at: "2012-04-28 19:50:56">]

However, if I run the SQL code directly on a query window of pgAdmin, I got correct result:

"2012-04-28 15:44:46.641305"
"2012-04-28 17:12:20.615641"
"2012-04-28 18:46:45.277561"
"2012-04-28 18:55:47.40109"
"2012-04-28 18:57:52.616501"
"2012-04-28 19:02:15.542964"
"2012-04-28 19:02:50.888847"
"2012-04-28 19:46:36.16556"
"2012-04-28 19:47:17.084047"
"2012-04-28 19:50:22.672805"
"2012-04-28 19:50:56.376571"

Note that those 5 records with created_at around "2012-04-28 03:14:29" at UTC is not in the correct result set.

Now the most puzzling question:
How can Rails send the same SQL statement to Postgres database and get a different result?

I am obviously missing something here. Can any guru help out?

Additional Notes 2012-5-2

Background: The rails 3.0 by default generated created_at column in all db table as "timestamp without time zone" in Postgres. I need to run a daily report based on the time zone of my server, 'America/New York'. Due to number of tables involved, I used ARel select method to join multiple tables and passed in begin_date and end_date timestamps. I have difficulty to get correct result set returned. It should return records with UTC timestamps:

2012-04-28 04:00:00    to   2012-04-29 04:00:00    

but it returned records with UTC timestamps:

2012-04-28 00:00:00    to   2012-04-29 00:00:00    

What I can figured out is to change the class method:

def self.transactions2(business_id, begin_date, end_date )

st_tz_offset      =   Time.zone.now.formatted_offset(true)
st_begin_date_tz  =   "#{begin_date} 00:00:00#{st_tz_offset}"
st_end_date_tz    =   "#{end_date} 00:00:00#{st_tz_offset}"

trans = LogVouchers.select( %{
      created_at
  } ).
where( %{
     created_at >= ( TIMESTAMP WITH TIME ZONE ? at time zone 'utc')
    and created_at < (TIMESTAMP WITH TIME ZONE ? at time zone 'utc')
  },
     st_begin_date_tz  ,
    st_end_date_tz  )

end

Which in fact, tells Postgres to translate that timestamp with time zone into timestamp without time zone before comparing. And that worked for me. I know this is too complicated. I would very much appreciate someone pointing out a better way to achieve this.

Additional Note 2

• If possible, I would avoid changing default setting in Ubuntu/Linux server, Postgres server as it tends to cause other side effects.
• In the future, these daily cutoff timestamps are based on the TimeZone setting of current_user instead of the TimeZone of server. Because users of same web app can be spanned over multiple time zones and the daily report has to make sense to respective logged in user.

Answer 1

Your expression depends on the local time setting. This:

SET  timezone = 'EST';
SELECT TIMESTAMP WITH TIME ZONE '2012-04-29' AT TIME ZONE 'UTC';

returns a different timestamp than this:

SET  timezone = '-2';
SELECT TIMESTAMP WITH TIME ZONE '2012-04-29' AT TIME ZONE 'UTC';

Obviously, you have a different time setting in the connection from pgAdmin than you have in the connection from ARel. Find out with:

SHOW timezone;

timezone is defined in postgresql.conf, but can be set to a different value at any time. You can reset it to the default with:

RESET timezone;

I explained the PostgreSQL handling of timestamps and time zones in more detail in this recent answer.
More about time zones in the manual.

---

If you want "midnight at UTC", use this expression instead:

SELECT TIMESTAMP '2012-04-29' AT TIME ZONE 'UTC';

Or simply:

SELECT '2012-04-29 0:0 +0'::timestamptz;

This returns the same internal value regardless of the timezone setting. It will displayed according to the local timezone, though. So with EST, you will see:

2012-04-28 19:00:00-05

---

Without time zone

According to your additional info, your table holds data of the type timestamp without time zone - or simply timestamp, as the default is without time zone. And these timestamps are implicitly based on EST.

If you want to retrieve data for one day, starting at midnight, ending at midnight, independent of the time zone you are currently in, do not use the data type timestamp with time zone (or timestamptz) at all. Just use timestamp and everything is groovy. The server should get:

WHERE created_at >= timestamp '2012-04-28 0:0'
AND   created_at <  timestamp '2012-04-29 0:0'

Or:

WHERE created_at >= '2012-04-28 0:0'::timestamp
AND   created_at <  '2012-04-29 0:0'::timestamp

Or even just:

WHERE created_at >= '2012-04-28 0:0'
AND   created_at <  '2012-04-29 0:0'

If you want a different slice of the data depending at your current time zone, then the server should get this:

WHERE created_at >= '2012-04-28 0:0'::timestamptz AT TIME ZONE 'EST'
AND   created_at <  '2012-04-29 0:0'::timestamptz AT TIME ZONE 'EST'

The term '2012-04-28 0:0'::timestamptz AT TIME ZONE 'EST' calculates the time in New York (without time zone) when it is midnight locally.

Or you can calculate the timestamps in your app accordingly and use the above query without time zone.