SQL'AT TIME ZONE'，查询范围很广，并且所有列（表名。*）都带有'SELECT' [英] SQL 'AT TIME ZONE', query wide and with a 'SELECT' all columns (tablename.*) expression

问题描述

在必要时，我在数据库中将 timestamp（3）与时区一起使用用于表，在我的情况下几乎是这种情况。

I use timestamp(3) with time zone in my database for the tables when one is necessary, this is almost any case in my situation.

我需要在比以下更为复杂的查询中选择表的所有列。

I need to select all columns of a table possibly in a much more complex query than the following.

我的问题是如何使用SQL 在时区'< TIMEZONE>'中获取所需时区的时间戳（带有时区），用于选择表达式，例如 q。 * 其中之一是 timestamp（带有时区）列。
我可能有相同情况的子查询。
是否有表达式可以实现整个查询？

My question is how to get the timestamps (with time zone) at the desired time zone with SQL at time zone '<TIMEZONE>' for a select expression like q.* one of which is the timestamp (with time zone) column. I might have subqueries with the same situation. Is there an expression to achieve this for query wide?

SELECT
    q.*, -- created_at timestamp (with time zone) is already in here
    q.created_at AT TIME ZONE 'EET', --instead of this redundant column selection
    u.name AS author,
    u.reputation,
    CASE WHEN count(t.*)=0 THEN '[]' ELSE json_agg(t.*) END as tags
FROM posts q

-- authors
JOIN users u
ON q.author_id = u.id

-- tags
left join post_has_tag p_h_t
on q.id = p_h_t.post_id
left join tags t
on p_h_t.tag_id = t.id

WHERE q.post_type = 'question'
group by q.id, u.id;

推荐答案

如果列的类型为 timestamp ，然后使用 AT时区是将它们转换为特定时区的正确方法。

If your columns are of type timestamp, then using AT TIME ZONE is the correct way to convert them to a specific time zone.

但是，请勿使用 EET 。使用此列表中基于特定地区的时区 ，例如 Europe / Bucharest -或其他适用于您的内容。

However, don't use EET. Use a specific locality-based time zone from this list, such as Europe/Bucharest - or whatever is applicable for you.

或者，如果您的列的类型为带有时区的时间戳记 ，然后您可以设置会话的时区，postgres会为您完成转换：

Alternatively, if your columns are of type timestamp with time zone, then you can set the session's time zone and postgres will do the conversion for you:

SET TIME ZONE 'Europe/Bucharest'

您应该阅读文档了解这两种时间戳类型的区别。

You should read up on the docs to understand the difference of these two timestamp types.

这篇关于SQL'AT TIME ZONE'，查询范围很广，并且所有列（表名。*）都带有'SELECT'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！