Activerecord Is Not Aware of Timezones

ActiveRecord is not aware of timezones?

create a migration with this content to remove the default value to created_at, so it's not filled by default on database level.

change_column_default(:user_stocks, :created_at, nil)

after that, when you create a new UserStock, you need to specify the created_at value, and there you can specify the created_at with the date of the user, taking care of the timezone.

UserStock.create(created_at: Time.now.in_time_zone(user.time_zone).to_time...)

or you can just maybe add it to a callback on the model so it's automatic everytime you create a UserStock, it change the value

class UserStock < ActiveRecord::Base
  before_create :set_created_at

  private
    def set_created_at
      self.created_at = time.now.in_time_zone(self.user.time_zone).to_time
    end
end

so everytime you create one, the value is correct. with that it may work as expected.

Another option if you don't need it just for that specific model and use them for all the user interaction, you can create a before filter on the application controller to set the time zone dinamically, something like

class ApplicationController < ActionController::Base
  before_filter :set_time_zone

  def set_time_zone(&block)
    time_zone = current_user.try(:time_zone) || 'UTC'
    Time.use_zone(time_zone, &block)
  end
end

Ignoring time zones altogether in Rails and PostgreSQL

Postgres has two different timestamp data types:

• timestamp with time zone, short name: timestamptz
• timestamp without time zone, short name: timestamp

timestamptz is the preferred type in the date/time family, literally. It has typispreferred set in pg_type, which can be relevant:

• Generating time series between two dates in PostgreSQL

Internal storage and epoch

Internally, timestamps occupy 8 bytes of storage on disk and in RAM. It is an integer value representing the count of microseconds from the Postgres epoch, 2000-01-01 00:00:00 UTC.

Postgres also has built-in knowledge of the commonly used UNIX time counting seconds from the UNIX epoch, 1970-01-01 00:00:00 UTC, and uses that in functions to_timestamp(double precision) or EXTRACT(EPOCH FROM timestamptz).

The source code:

* Timestamps, as well as the h/m/s fields of intervals, are stored as
* int64 values with units of microseconds.  (Once upon a time they were  
* double values with units of seconds.)

And:

/* Julian-date equivalents of Day 0 in Unix and Postgres reckoning */  
#define UNIX_EPOCH_JDATE        2440588 /* == date2j(1970, 1, 1) */  
#define POSTGRES_EPOCH_JDATE    2451545 /* == date2j(2000, 1, 1) */  

The microsecond resolution translates to a maximum of 6 fractional digits for seconds.

timestamp

For timestamp no time zone is provided explicitly. Postgres ignores any time zone modifier added to the input literal by mistake!

No hours are shifted for display. With everything happening in the same time zone this is fine. For a different time zone the meaning changes, but value and display stay the same.

timestamptz

Handling of timestamptz is subtly different. I quote the manual here:

For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time ...)

Bold emphasis mine. The time zone itself is never stored. It is an input modifier used to compute the according UTC timestamp, which is stored - or and output decorator used to compute the local time for display - with appended time zone offset. If you don't append an offset for timestamptz on input, the current time zone setting of the session is assumed. All computations are done with UTC timestamp values. If you (may) have to deal with more than one time zone, use timestamptz. In other words: If there can be any doubt or misunderstanding about the assumed time zone, go with timestamptz. Applies in most use cases.

Clients like psql or pgAdmin or any application communicating via libpq (like Ruby with the pg gem) are presented with the timestamp plus offset for the current time zone or according to a requested time zone (see below). It is always the same point in time, only the display format varies. Or, as the manual puts it:

All timezone-aware dates and times are stored internally in UTC. They
are converted to local time in the zone specified by the TimeZone
configuration parameter before being displayed to the client.

Example in psql:

db=# SELECT timestamptz '2012-03-05 20:00+03';
      timestamptz
------------------------
 2012-03-05 18:00:00+01

What happened here?

I chose an arbitrary time zone offset +3 for the input literal. To Postgres, this is just one of many ways to input the UTC timestamp 2012-03-05 17:00:00. The result of the query is displayed for the current time zone setting Vienna/Austria in my test, which has an offset +1 during winter and +2 during summer time ("daylight saving time", DST). So 2012-03-05 18:00:00+01 as DST only kicks in later.

Postgres forgets the input literal immediately. All it remembers is the value for the data type. Just like with a decimal number. numeric '003.4' or numeric '+3.4' - both result in the exact same internal value.

AT TIME ZONE

All that's missing now, is a tool to interpret or represent timestamp literals according to a specific time zone. That's where the AT TIME ZONE construct comes in. There are two different use cases. timestamptz is converted to timestamp and vice versa.

To enter the UTC timestamptz 2012-03-05 17:00:00+0:

SELECT timestamp '2012-03-05 17:00:00' AT TIME ZONE 'UTC'

... which is equivalent to:

SELECT timestamptz '2012-03-05 17:00:00 UTC'

To display the same point in time as EST timestamp (Eastern Standard Time):

SELECT timestamp '2012-03-05 17:00:00' AT TIME ZONE 'UTC' AT TIME ZONE 'EST'

That's right, AT TIME ZONE 'UTC' twice. The first interprets the timestamp value as (given) UTC timestamp returning the type timestamptz. The second converts the timestamptz to the timestamp in the given time zone 'EST' - what a wallclock displays in the time zone EST at this point in time.

Examples

SELECT ts AT TIME ZONE 'UTC'
FROM  (
   VALUES
      (1, timestamptz '2012-03-05 17:00:00+0')
    , (2, timestamptz '2012-03-05 18:00:00+1')
    , (3, timestamptz '2012-03-05 17:00:00 UTC')
    , (4, timestamp   '2012-03-05 11:00:00'  AT TIME ZONE '+6') 
    , (5, timestamp   '2012-03-05 17:00:00'  AT TIME ZONE 'UTC') 
    , (6, timestamp   '2012-03-05 07:00:00'  AT TIME ZONE 'US/Hawaii')  -- ①
    , (7, timestamptz '2012-03-05 07:00:00 US/Hawaii')                  -- ①
    , (8, timestamp   '2012-03-05 07:00:00'  AT TIME ZONE 'HST')        -- ①
    , (9, timestamp   '2012-03-05 18:00:00+1')  -- ② loaded footgun!
      ) t(id, ts);

Returns 8 (or 9) identical rows with a timestamptz columns holding the same UTC timestamp 2012-03-05 17:00:00. The 9th row sort of happens to work in my time zone, but is an evil trap. See below.

① Rows 6 - 8 with time zone name and time zone abbreviation for Hawaii time are subject to DST (daylight saving time) and might differ, though not currently. A time zone name like 'US/Hawaii' is aware of DST rules and all historic shifts automatically, while an abbreviation like HST is just a dumb code for a fixed offset. You may need to append a different abbreviation for summer / standard time. The name correctly interprets any timestamp at the given time zone. An abbreviation is cheap, but needs to be the right one for the given timestamp:

• Time zone names with identical properties yield different result when applied to timestamp

_{Daylight Saving Time is not among the brightest ideas humanity ever came up with.}

② Row 9, marked as loaded footgun works for me, but only by coincidence. If you explicitly cast a literal to timestamp [without time zone], any time zone offset is ignored! Only the bare timestamp is used. The value is then automatically coerced to timestamptz in the example to match the column type. For this step, the timezone setting of the current session is assumed, which happens to be the same time zone +1 in my case (Europe/Vienna). But probably not in your case - which will result in a different value. In short: Don't cast timestamptz literals to timestamp or you lose the time zone offset.

Your questions

User stores a time, say March 17, 2012, 7pm. I don't want timezone
conversions or the timezone to be stored.

Time zone itself is never stored. Use one of the methods above to enter a UTC timestamp.

I only use the users specified time zone to get records 'before' or
'after' the current time in the users local time zone.

You can use one query for all clients in different time zones.

For absolute global time:

SELECT * FROM tbl WHERE time_col > (now() AT TIME ZONE 'UTC')::time

For time according to the local clock:

SELECT * FROM tbl WHERE time_col > now()::time

Not tired of background information, yet? There is more in the manual.

ActiveRecord query on dates in user's time zone

Thanks to this question I found an answer:

Getting date with timezone offset in postgres

I basically needed to convert the datetime to UTC first, then to the user time zone.

Event.where("DATE(datetime AT TIME ZONE 'UTC' AT TIME ZONE ?) = ?", Time.now.in_time_zone(current_user.time_zone).strftime("%Z"), Time.zone.today)

Rails where() and timezones

You're losing track of the timezone information as soon as you call the DATE() function in your SQL. There are two things to keep in mind:

1. Everything inside the database will be in UTC and everything includes the result of DATE().
2. A single date (i.e. day) in your local time zone can easily cross two dates in UTC so you need to look at the entire timestamp (on both sides) rather than just the (UTC) date components.

Something like this should work:

now = Time.now
Entry.where('created_at between :start and :end and email = :email',
    :start => now.beginning_of_day,
    :end   => now.end_of_day,
    :email => email
)

The times should be converted to UTC automatically. You might want to handle the upper bound a little differently; using end_of_day gives you 23:59:59 so there is a little room for something to slip through that you want to capture. You can get around that problem using an explicit half-open interval (rather than the closed interval that SQL's between uses) like this:

now = Time.now
Entry.where('created_at >= :start and created_at < :end and email = :email',
    :start => now.beginning_of_day,
    :end   => now.tomorrow.beginning_of_day,
    :email => email
)

Why does this rails query behave differently depending on timezone?

The Time class isn't directly aware of your configured timezone. Rails 2.1 added a bunch of timezone support, but Time will still act upon your local timezone. This is why Time.now returns a BST time.

What you likely want is to interact with Time.zone. You can call methods on this like you would the Time class itself but it will return it in the specified time zone.

Time.zone.now # => Tue, 11 Aug 2009 21:31:45 UTC +00:00
Time.zone.parse("2:30 PM Aug 23, 2009") # => Sun, 23 Aug 2009 14:30:00 UTC +00:00

Another thing you have to be careful with is if you ever do queries on the database where you are comparing times, but sure to use the UTC time (even if you have a different time zone specified) because Rails always stores UTC in the database.

Item.all(:conditions => ["published_at <= ?", Time.now.utc])

Also, instead of Time.now-1.hour do 1.hour.ago. It is easier to read and Rails will automatically use the configured timezone.

---

Related Topics