1

Events have many shifts.

My shifts table which contains a starts_at and ends_at column which are DATETIME format.

If I query the shifts table, the starts_at and ends_at columns return a string which contains timezone information and is recognised by Rails as an ActiveSupport::TimeWithZone object -- and I can manipulate it accordingly.

If I include the shifts.starts_at or shifts.ends_at column in a more complex query involving a join, I seem to be losing the timezone info in the result set. Consider the following query:

SELECT events.id, events.name, events.default_shift_start,
shifts.id AS shift_id, shifts.starts_at, shifts.ends_at, users.id AS user_id,
users.first_name, users.last_name 
FROM "events" 
INNER JOIN "shifts" ON "shifts"."event_id" = "events"."id" 
INNER JOIN "requests" ON "requests"."shift_id" = "shifts"."id" 
INNER JOIN "users" ON "users"."id" = "requests"."user_id" 
WHERE (events.default_shift_start > '2012-08-22 05:55:22.069340' AND requests.status = 'accepted') 
ORDER BY default_shift_start ASC

EDIT: I'm calling this query in Rails by way of:

Event.joins(:shifts => { :requests => :user}).where(["events.default_shift_start > ? AND requests.status = ?", Time.now, "accepted"]).select("events.id, events.name, events.default_shift_start, shifts.id AS shift_id, shifts.starts_at, shifts.ends_at, users.id AS user_id, users.first_name, users.last_name").order("default_shift_start ASC")

Produces:

+-----+----------+---------------------+---------------------+
| id  | shift_id | starts_at           | ends_at             |
+-----+----------+---------------------+---------------------+
| 17  | 80       | 2012-08-23 00:30:00 | 2012-08-23 07:30:00 |
| 17  | 55       | 2012-08-23 00:30:00 | 2012-08-23 07:30:00 |
+-----+----------+---------------------+---------------------+

The issue is that the columns from the JOINed table (shifts) aren't returning any timezone data, causing Rails to recognize them as String data. The data is stored in the database as UTC. If I include a datetime column from the events table in the same query, it includes timezone data in the result.

I've been searching through the documentation trying to understand what's going on here, but to no avail.

Andrew Reid
  • 13
  • 4
  • Please show your table definitions, either `CREATE TABLE` statements or the output of `\d tablename` in psql. Is it possible that you're using a mix of `timestamp` and `timestamptz` types, and Rails only likes one of them? – Craig Ringer Aug 22 '12 at 06:22
  • @CraigRinger: Rails implies `timestamp without time zone` and UTC values in the database. – mu is too short Aug 22 '12 at 06:28
  • Table definitions [here](https://gist.github.com/3424581) as requested. The columns are `timestamp without timezone` format -- this does indeed seem to be a Rails issue, as opposed to a Postgres one. For some reason, the column selected from the JOINed table isn't being recognised as a the usual type. Note that if I select from the shifts table directly, I have no such problem. – Andrew Reid Aug 22 '12 at 11:20

2 Answers2

2

I guess you should start with getting a grasp on the involved data types. The "datetime" data type in PostgreSQL is actually timestamp and there are two variants: with and without time zone. The default is without.

Internally, PostgreSQL always stores a UTC timestamp. Time zone data itself is not saved at all with timestamps, neither with nor without time zone. Those are just decorators for input and output that accommodate for the timezone setting of the client.

Find a more detailed explanation, examples and links at this related answer.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 1
    Thanks, Erwin. I'm presently working around the issue by converting the resulting `String` using `to_time` in Rails. I'm convinced that this is a Railsism, to do with the fact that the joined column (from the `shifts` table) doesn't exist in the schema of the object I'm using to query (`Event`). – Andrew Reid Aug 22 '12 at 11:25
0

i think if you put starts_at::timestamptz then you are good.

Kasumi
  • 911
  • 5
  • 15
  • my guess is that you ask events to give u timestamptz by default, but since in your query you do a comparision via string, it converted the column into string. try '2012-08-22 05:55:22.069340'::timestamp as well. – Kasumi Aug 22 '12 at 08:24
  • It does indeed return timezone data in the result - but Rails still presents it as a `String`. Again, thinking more and more that this is because Rails can't find `starts_at` column in the schema for the `events` table (because it is a column from the `shifts` table being accessed by a JOIN). Don't know if this is a "bug" or not, but I guess I have to work around it or look at the Rails codebase to see if its an issue there. – Andrew Reid Aug 22 '12 at 11:32