group by date and two different times in the same date in Sql

Tags: , , , ,



i want to select from my database the data that is

BETWEEN to date and two different times in the same date

but when I use the Sum in one of the variants it give me a wrong result

i thnik that the problem is in the group by

this is my exemple

date_0 = str(self.dateEdit_49.text())
        date_1 = str(self.dateEdit_47.text()) 

        selected_day = self.dateEdit_49.date()
        last_day = selected_day.addDays(-1)
        last_day_str = last_day.toString(self.dateEdit_49.displayFormat())

'''SELECT date_d, SUM(montant) FROM transactions 
WHERE (date_d, time_d) > (%s::date, '16:00:00'::time) 
AND (date_d, time_d) < (%s::date, '16:00:00'::time) 
GROUP BY (date_d)''',(last_day_str, date_1))

how can i groupe the result with date but date is like this image enter image description here

Answer

You seem to want:

SELECT date_d, SUM(montant) 
FROM transactions 
WHERE date_d + time_d > %s::date + '16:00:00'::time
  AND date_d + time_d < %s::date + '16:00:00'::time
GROUP BY date_d

If you want to generate groups of rows that start at 4 PM until the next day at 4 PM, then we can offset the dates. A subquery comes handy for this:

SELECT new_dt, SUM(montant) 
FROM (
    SELECT t.*, date_d + time_d - interval '16 hour' new_dt 
    FROM transactions t
) t
WHERE new_dt > %s::date
  AND new_dt < %s::date
GROUP BY new_dt


Source: stackoverflow