英文:
How to subtract dates in postgres sql with inclusiveness in both dates
问题
我尝试在PostgreSQL中减去两个日期。例如，2022-10-31和2022-11-1。所以我采取了以下方法。
select (date_column1 - date_column_2) as date_diff
from table;
我的预期输出是2天（列的数据类型为interval）。但我只看到1天作为输出。
英文:
I am trying to subtract two dates in postgres sql. example 2022-10-31 and 2022-11-1. So I did below approach.
select (date_column1 - date_column_2) as date_diff
from table;
My expected output is 2 days (data type of column is interval). But I'm only seeing 1 day as output.
答案1
得分: 2
The difference is 1 day, so if you need 2 as the answer simply add 1 to the result.
select ('2023-11-01':: Date - '2023-10-31' ::date) +1 as date_diff
;
| date_diff |
|---|
| 2 |
[fiddle](https://dbfiddle.uk/dIYx_xsa)
<details>
<summary>英文:</summary>
The difference is 1 day, so if you need 2 as answer simply add 1 to the result
select ('2023-11-01':: Date - '2023-10-31' ::date) +1 as date_diff
;
| date\_diff |
|----------:|
| 2 |
[fiddle](https://dbfiddle.uk/dIYx_xsa)
</details>
# 答案2
**得分**: 0
什么是42和43之间的区别？它是`一个`。
但当然，42和43是`两个`相邻的数字。
这是关于篱笆柱和它们之间距离的古老问题：如果篱笆由两根柱子组成，两根柱子之间的距离为一米，那么篱笆的长度为一米。
<details>
<summary>英文:</summary>
What's the difference between 42 and 43?. It's `one` .
But, of course, 42 and 43 are `two` adjacent numbers.
It's the old question of the fence posts and the distance between them: If the fence consists of two posts and the distance between two posts is one meter, the fence is one meter long.
</details>
# 答案3
**得分**: 0
以下是翻译好的部分：
你实际上正在执行：
```sql
select '11/01/2022 00:00:00'::timestamptz - '10/31/2022 00:00:00'::timestamptz;
?column?
----------
1 day
换句话说，从午夜到午夜，通常是一个24小时的时间段，以下是一些例外情况。
你可以在夏令时改变时最清楚地看到这一点。在美国，夏令时分别发生在2023年3月12日和2023年11月5日，因此：
-- 改变前的一天
select '03/12/2023 00:00:00'::timestamptz - '03/11/2023 00:00:00'::timestamptz;
?column?
----------
1 day
-- 夏令时变化当天
select '03/13/2023 00:00:00'::timestamptz - '03/12/2023 00:00:00'::timestamptz;
?column?
----------
23:00:00
-- 切换回标准时间的那一天
select '11/06/2023 00:00:00'::timestamptz - '11/05/2023 00:00:00'::timestamptz;
?column?
----------------
1 day 01:00:00
英文:
You are effectively doing:
select '11/01/2022 00:00:00'::timestamptz - '10/31/2022 00:00:00'::timestamptz;
?column?
----------
1 day
In other words Midnight to Midnight which is generally a 24 hour period, with exceptions below.
You can see that best across a Day light savings time change. For here(USA) that occurred on March 12, 2023 and November 5, 2023 so:
--Day before change
select '03/12/2023 00:00:00'::timestamptz - '03/11/2023 00:00:00'::timestamptz;
?column?
----------
1 day
--Day of change to DST
select '03/13/2023 00:00:00'::timestamptz - '03/12/2023 00:00:00'::timestamptz;
?column?
----------
23:00:00
--Day of change back to standard time
select '11/06/2023 00:00:00'::timestamptz - '11/05/2023 00:00:00'::timestamptz;
?column?
----------------
1 day 01:00:00
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论