postgresql - 将日期时间列转换为不同时区的查询

Question

我正在尝试组合日期、时间和时区，然后将其转换为 UTC 时区。

在下面的查询中，使用存储时区的数据库字段创建了一个错误——即 t.timezone

t.timezone 的值为 Asia/Tokyo 。所有其他时区都采用类似的格式。

select (select CONCAT( DATE(m.last_mailing at time zone 'utc' + INTERVAL '1 day'), ' ', MAKE_TIME(m.email_delivery_hour::int, 0, 0))::timestamp with time zone t.timezone) at time zone 'utc' FROM acts2301_membership.member_subscriptions m INNER JOIN acts2301_email_queue.timezone t ON (m.timezone_reference = t.reference) WHERE m.subscription_reference = '514';

Answer 1

以下是按照您问题中的逻辑执行的查询。但是，这在很大程度上取决于各种数据和服务器的时区设置方式(另请参阅我对您的问题的评论)，因此您可能会发现必须调整指定的位置 t.timezone 以及 UTC 的位置:

SELECT ((date_trunc('day', (m.last_mailing AT TIME ZONE 'UTC')::timestamp) + 
         interval ('1 day') +
         (m.email_delivery_hour::int::text || ':00:00')::time
        )::timestamptz AT TIME ZONE t.timezone
       )::timestamptz AT TIME ZONE 'UTC'
FROM acts2301_membership.member_subscriptions m 
JOIN acts2301_email_queue.timezone t ON m.timezone_reference = t.reference
WHERE m.subscription_reference = '514';

还要注意适当的代码格式之美...