Postgres truncate timestamp给出了稍微意外的输出

Question

我有一个名为device_data的表它看起来像下面

Column      |            Type             | Collation | Nullable | Default 
------------------+-----------------------------+-----------+----------+---------
id               | integer                     |           |          | 
date             | timestamp without time zone |           |          | 
upload           | real                        |           |          | 
download         | real                        |           |          | 

现在，当我对数据进行每日截断时，我在

下面这样做第一次查询

SELECT
date_trunc('day', date) as daily,
avg(upload) as avg_upload,
avg(download) as avg_download
FROM device_info
WHERE
date BETWEEN '2022-10-07 10:28:46' AND '2022-11-06 10:28:46'
GROUP BY daily
ORDER BY daily;

我得到下面的结果

daily        |     avg_upload     |    avg_download    
---------------------+--------------------+--------------------
2022-10-07 00:00:00 | 41.691493286006484 | 41.571846902122246
2022-10-08 00:00:00 | 23.348292880412536 | 25.450085919814388
2022-10-09 00:00:00 | 14.496747577637498 |  15.03089042501744
2022-10-10 00:00:00 | 108.54434560738528 |   296.091238792341
2022-10-11 00:00:00 |  163.0374394524876 |  140.3484673696004
2022-10-12 00:00:00 | 240.54915519812943 |   74.1109145983748
2022-10-13 00:00:00 |  94.02086876518116 | 162.71920269686137
2022-10-14 00:00:00 | 58.254662611037375 | 62.832775704871615
2022-10-15 00:00:00 | 33.759871942986635 |  27.21136256905281
...

现在如果我运行下面的查询，我假设它做同样的事情，我得到的结果略有不同

第二个查询

SELECT
date_trunc('month', date) + date_part('day', date)::int / 1 * interval '1 day' AS daily,
avg(upload) as avg_upload,
avg(download) as avg_download
FROM device_info
WHERE
date BETWEEN '2022-10-07 10:28:46' and '2022-11-06 10:28:46'
GROUP BY daily
ORDER BY daily ASC;

我得到下面的结果

daily         |     avg_upload     |    avg_download    
---------------------+--------------------+--------------------
2022-10-08 00:00:00 | 41.691493286006484 | 41.571846902122246
2022-10-09 00:00:00 | 23.348292880412536 | 25.450085919814388
2022-10-10 00:00:00 | 14.496747577637498 |  15.03089042501744
2022-10-11 00:00:00 | 108.54434560738528 |   296.091238792341
2022-10-12 00:00:00 |  163.0374394524876 |  140.3484673696004
2022-10-13 00:00:00 | 240.54915519812943 |   74.1109145983748
2022-10-14 00:00:00 |  94.02086876518116 | 162.71920269686137
2022-10-15 00:00:00 | 58.254662611037375 | 62.832775704871615
2022-10-16 00:00:00 | 33.759871942986635 |  27.21136256905281

因此您可以看到，尽管两个输出看起来很相似，但第二个查询将数据移动了一天(

)第一行查询

2022-10-07 00:00:00 | 41.691493286006484 | 41.571846902122246

第一行查询

2022-10-08 00:00:00 | 41.691493286006484 | 41.571846902122246

那么我的第二个查询到底在哪里出错(我的假设是两个查询都做同样的事情)?

注意:我问这个问题的原因是因为我想要数据的weekly截断和数据的6 days截断，第二个查询可以派上用场。

Answer 1

那么我的第二个查询到底在哪里出错?

您假设date_trunc()返回一个时间戳，其中月份的日期设置为零，当然不是，它返回当月的第一天。所以你必须减少一天的碳补偿:

...
date_trunc('month', date) + (date_part('day', date)::int - 1) / 1 * interval '1 day' AS daily,
...

记住:在计算机科学中只有两个难题:

• 缓存失效
• 命名的东西
"从一开始">
• 错误