CODEWITHSUNDEEP
sqlpostgresqldateselect
Manu
Manu

Reputation: 1120

Time difference based on Sundays in PostgreSQL

I have the following table:

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')

I want the date difference but some of the payment days the_debt_due fall on Sunday (like June 07). If the payment day fall on Sunday, I want to add 1 to the the_debt_due, so the difference for good payers is 0 or negative (paid in advance).

The result should follow:

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

But I don't know how to tell what day is Sunday to the previous query.

Expected output

date_diff
1
0
-1
-4
0

Note that the second row is 0 because the due date fell on Sunday.

Any help will be greatly appreciated.

Upvotes: 1

Views: 56

Answers (1)

GMB
GMB

Reputation: 222582

You can use (extract dow from ...) to get the day of the week of a given date, where Sunday is day of week 0.

This would do what you want:

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

Demo on DB Fiddle:

the_debt_id | the_debt_paid       | the_debt_due | date_diff
:---------- | :------------------ | :----------- | --------:
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

Upvotes: 2

Related Questions