Rails + Postgres：未从联接表中返回列的时区信息 [英] Rails + Postgres: Not returning timezone info on column from joined table

问题描述

事件有很多班次。

我的班次表包含 starts_at 和



如果我查询班次表，则 starts_at 和 ends_at 列返回一个包含时区信息的字符串，Rails将该字符串识别为 ActiveSupport :: TimeWithZone 对象- -并且我可以相应地进行操作。

如果我包含 shifts.starts_at 或在涉及联接的更复杂的查询中的shifts.ends_at 列中，我似乎在结果集中丢失了时区信息。考虑以下查询：

  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 =事件。 id 
 INNER JOIN请求在 requests上。 shift_id =班次。 id 
 INNER JOIN用户在用户上。 id = requests。 user_id 
在哪里（events.default_shift_start>'2012-08-22 05：55：22.069340'AND requests.status ='accepted'）
由default_shift_start ASC排序
  

编辑：我通过以下方式在Rails中调用此查询：

  Event.joins（：shifts => {：requests =>：user}）。where（[ events。 default_shift_start>？AND request.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 A S user_id，users.first_name，users.last_name）。order（ default_shift_start ASC）
  

产生：

 
 + ----- + ---------- + --------- ------------ + --------------------- + 
 | 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 | 
 + ----- + ---------- + --------------------- + ------ --------------- + 
 

问题是JOINed表中的列（ shifts ）未返回任何时区数据，导致Rails将其识别为String数据。数据作为UTC存储在数据库中。如果我在同一查询的事件表中包含 datetime 列，则结果中将包括时区数据。 / p>

我一直在搜索文档，试图了解此处的情况，但无济于事。

解决方案

我想您应该首先了解所涉及的数据类型。 PostgreSQL中的 datetime数据类型实际上是 时间戳，有两种变体：有和无时区 。默认值为，没有。

在内部，PostgreSQL总是存储UTC时间戳。时区数据本身完全没有时间戳，没有时间戳也没有时间戳。那些仅仅是用于输入和输出的装饰器，可以适应客户端的时区设置。

查找更多的此相关答案的详细说明，示例和链接。

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.

解决方案

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.

这篇关于Rails + Postgres：未从联接表中返回列的时区信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！