sql - PostgreSQL 中基于星期日的时差

Question

我有下表：

CREATE TABLE my_table 
(
    the_debt_id varchar(6) NOT NULL, 
    the_debt_paid timestamp NOT NULL, 
    the_debt_due date NOT NULL
)

INSERT INTO my_table
VALUES ('LMUS01', '2019-05-03 09:00:01', '2019-05-02'), 
       ('LMUS01', '2019-06-03 10:45:12', '2019-06-02'), 
       ('LMUS01', '2019-07-01 15:39:58', '2019-07-02'), 
       ('LMUS02', '2019-05-03 19:43:44', '2019-05-07'), 
       ('LMUS02', '2019-06-07 08:37:05', '2019-06-07')

我想要日期差异，但某些付款the_debt_due日落在星期日（如 6 月 7 日）。如果付款日是星期天，我想在 上加 1 the_debt_due，所以好的付款人的差是 0 或负数（预付）。

结果应如下所示：

SELECT (date(the_debt_paid) - the_debt_due) AS date_diff 
FROM my_table

但是我不知道如何判断上一个查询是星期天是星期天。

预期产出

date_diff
1
0
-1
-4
0

请注意，第二行是 0，因为截止日期是星期日。

任何帮助将不胜感激。

标签： sqlpostgresqldateselect

Answer 1

您可以使用(extract dow from ...)获取给定日期的星期几，其中星期日是第 0 周。

这会做你想要的：

select 
    t.*, 
    date(the_debt_paid) 
        - the_debt_due 
        - (extract(dow from the_debt_due) = 0)::int AS date_diff 
from my_table t

DB Fiddle 上的演示：

the_debt_id | the_debt_paid | the_debt_due | 日期差异
:------------ | :----------------- | :----------- | --------:
LMUS01      | 2019-05-03 09:00:01 | 2019-05-02   |         1
LMUS01      | 2019-06-03 10:45:12 | 2019-06-02   |         0
LMUS01      | 2019-07-01 15:39:58 | 2019-07-02   |        -1
LMUS02      | 2019-05-03 19:43:44 | 2019-05-07   |        -4
LMUS02      | 2019-06-07 08:37:05 | 2019-06-07   |         0