当前位置：文江博客话题详情

postgresql：对没有时区的时间戳进行时区敏感查询

发布于 2024-10-22 23:29:13 字数 204 浏览 1 评论 0原文

我有一个带有 timestamp without time zone 列的表（假设输入的数据位于澳大利亚/悉尼时区）。

查询 America/New_York 时区某个时间范围（即上午 8 点至下午 4 点）的数据。

有没有一种简单的方法可以实现这一目标？

谢谢，p。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

我的奇迹 2024-10-29 23:29:14

您可以将所有内容转换为相同的时区，以便您可以将它们进行比较（如果设置了时区）：

select current_time, current_time at time zone 'gmt';
      timetz       |     timezone      
-------------------+-------------------
 20:50:51.07742-07 | 03:50:51.07742+00

如果未设置时区并且您需要更正一些当地时间：

select now()::time, now()::time + '+8:00'::interval;
       now       |    ?column?     
-----------------+-----------------
 20:57:49.420742 | 04:57:49.420742

一旦您按照您想要的方式获得时间，只需提取小时，您就可以使用简单的条件来选择合适的时间。

select * 
  from 
  (select extract(hour from now()::time + '+8:00'::interval) as hour) as t 
  where hour between 8 and 16;

You can convert everything to the same time zone so you can compare them with (if the timezone was set):

select current_time, current_time at time zone 'gmt';
      timetz       |     timezone      
-------------------+-------------------
 20:50:51.07742-07 | 03:50:51.07742+00

If the time zone is not set and you need to correct it some local time:

select now()::time, now()::time + '+8:00'::interval;
       now       |    ?column?     
-----------------+-----------------
 20:57:49.420742 | 04:57:49.420742

Once you get the time the way you want, just the extract the hour and you can use a simple condition to select the proper times.

select * 
  from 
  (select extract(hour from now()::time + '+8:00'::interval) as hour) as t 
  where hour between 8 and 16;

回复收藏 0 原文

魔法少女 2024-10-29 23:29:13

想通了。

您需要首先将时间转换为 with time zone 版本，即 my_ts at time zone 'Australia/Sydney'，然后通过 at 将其转换为纽约对应时间时区“America/New_York”

select
    my_ts as "Default(syd)",
    my_ts at time zone 'Australia/Sydney' as "SYD",
    my_ts at time zone 'Australia/Sydney' at time zone 'America/New_York' as "NY",
    date_part('hour', my_ts at time zone 'Australia/Sydney' at time zone 'America/New_York') as "NY-hr"
from my_table
where date_part('hour', my_ts at time zone 'Australia/Sydney' at time zone 'America/New_York')>=8
    and date_part('hour', my_ts at time zone 'Australia/Sydney' at time zone 'America/New_York')<16

Figured it out.

You need to first convert the time to it's with time zone version ie my_ts at time zone 'Australia/Sydney' and then convert that to it's NY counterpart via at time zone 'America/New_York'

select
    my_ts as "Default(syd)",
    my_ts at time zone 'Australia/Sydney' as "SYD",
    my_ts at time zone 'Australia/Sydney' at time zone 'America/New_York' as "NY",
    date_part('hour', my_ts at time zone 'Australia/Sydney' at time zone 'America/New_York') as "NY-hr"
from my_table
where date_part('hour', my_ts at time zone 'Australia/Sydney' at time zone 'America/New_York')>=8
    and date_part('hour', my_ts at time zone 'Australia/Sydney' at time zone 'America/New_York')<16

回复收藏 0 原文

~没有更多了~