CODEWITHSUNDEEP
pythonsqlpostgresql
houhou
houhou

Reputation: 177

SELECT BETWEEN to date and two different times in the same date

i'm using python and postgresql and pyqt5

my table in my database is like this :

CREATE TABLE IF NOT EXISTS transactions (
            id SERIAL PRIMARY KEY  UNIQUE NOT NULL,
            montant DECIMAL(100,2), 
            acte VARCHAR,
            date_d DATE, 
            time_d TIME )

I want to select data from this table between (date_d >= 2020-09-25 and time_d > 16:00:00) AND (date_d >= 2020-09-29 and time_d 16:00:00) and how can we calculate the sum of montant column of the same query

the following image demonstrate exactly the period that i want to select

enter image description here

Upvotes: 0

Views: 123

Answers (2)

Adrian Klaver
Adrian Klaver

Reputation: 19570

As example:


SELECT 
   sum(montant)
FROM 
    transactions
WHERE 
    (date_d + time_d) <@ tsrange('2020-09-25 16:00:00', '2020-09-29 16:00:00', '[]')

Combine the date and time using + and then test(<@) that it is in the timestamp(tsrange) you are interested in. The [] makes the endpoints inclusive. This assumes that date_d and time_d are date and time types respectively. If they are varchar cast them to the types.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

One method is to use tuples:

(date_d, time_d) >= ('2020-09-25'::date, '16:00:00'::time) AND ('2020-09-29'::date, '16:00:00'::time)

Of course, this includes the endpoints, which between suggests. Alternatively:

(date_d, time_d) > ('2020-09-25'::date, '16:00:00'::time) AND
(date_d, time_d) < ('2020-09-29'::date, '16:00:00'::time)

Upvotes: 2

Related Questions