postgresql Postgres DATE_TRUNC未按预期运行-显示错误数据

agxfikkp 于 2023-02-15 发布在 PostgreSQL

关注(0)|答案(2)|浏览(149)

我有以下记录创建于（_T）：

2022-11-13 14:24:02.651809
2022-11-13 15:00:43.878400
2022-11-13 16:33:18.710288
2022-11-13 18:22:59.220246
2022-11-13 18:50:59.719803
2022-11-15 20:33:19.099264
2022-11-16 15:42:51.285751
2022-11-16 15:46:28.196001
2022-11-16 16:06:31.873187
2022-11-16 16:47:32.071435
2022-11-16 19:05:38.288788

当我跑步时：

SELECT
DATE_TRUNC('day', CAST(forms.created_at AS DATE) AT TIME ZONE 'UTC') AT TIME ZONE 'EST' as "created_at",
count('created_at') AS total_forms
FROM forms
WHERE
(forms.created_at AT TIME ZONE 'UTC') AT TIME ZONE 'EST' BETWEEN '2022-11-13 00:00:00'::timestamp AND '2022-11-17 00:00:00'::timestamp
GROUP BY 1
ORDER BY created_at asc;

它返回以下内容：

2022-11-13 05:00:00.000000 +00:00,5
2022-11-16 05:00:00.000000 +00:00,5
2022-11-17 05:00:00.000000 +00:00,1

这是不正确的，因为它应该是：

2022-11-13, 5
2022-11-15, 1
2022-11-16, 5

我不知道为什么它没有显示2022 - 11 - 15。有人知道我该怎么解决这个问题吗？
我尝试过使用各种形式的CAST

postgresql

来源：https://stackoverflow.com/questions/75422607/postgres-date-trunc-not-behaving-as-expected-incorrect-data-showing

2条答案

按热度按时间

sh7euo9m1#

date_trunc总是返回时间戳，而不是日期。
如果只需要当前时区中的日期，请强制转换为日期。

SELECT
  cast(created_at as date) as created_at,
  count(created_at)
FROM forms
group by 1
ORDER BY created_at asc;

如果希望日期位于特定时区，则使用该时区的date_trunc转换为日期。

SELECT
  cast(date_trunc('day', created_at, 'Australia/Sydney') as date) as created_at,
  count(created_at)
FROM forms
group by 1
ORDER BY created_at asc;

Demonstration.

赞(0）回复(0）举报 2023-02-15

axr492tv2#

这实际上是一个非常简单的修复。我必须在DATE_TRUNC中 Package 时区。所以我必须更改

DATE_TRUNC('day', CAST(forms.created_at AS DATE) AT TIME ZONE 'UTC') AT TIME ZONE 'EST' as "created_at",

到这个

DATE_TRUNC('day', (forms.created_at AT TIME ZONE 'UTC') AT TIME ZONE 'EST') AS "created_at",

赞(0）回复(0）举报 2023-02-15

我来回答

postgresql Postgres DATE_TRUNC未按预期运行-显示错误数据

2条答案

相关问题

热门标签

最新问答