All we need is an easy explanation of the problem, so here it is.
i have a table with the following data
I tried the following SQL ( I’m using PostgreSQL ) but it always returning empty records
I need the interval to be changeable and i can get the count of the inspected and rejected rows between two dates.
SELECT TIMESTAMP WITH TIME ZONE 'epoch' + INTERVAL '5 minutes' * round((extract('epoch' FROM created_at) / 1800) * 1800)/60 AS clock,inspected , rejected,created_at FROM public."Mytable" WHERE created_at > localtimestamp - INTERVAL '1 week' AND created_at BETWEEN 2021-06-09 13:28:00.115348+03 AND 2021-06-10 13:28:00.115348+03 GROUP BY round(extract('epoch' FROM created_at) / 1800),created_at,inspected , rejected,created_at order by clock
now in the fiddle i could achieve what i want but the interval_alias calculation return a time stamp with timezone offset of +1 hour and i don’t know form were it comes( the original created_at column is +3 same as the server timezone)
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Your problem is the combination of these two conditions:
WHERE created_at > localtimestamp - INTERVAL '1 week' AND created_at BETWEEN 2021-06-09 13:28:00.115348+03 AND 2021-06-10 13:28:00.115348+03
created_at > localtimestamp - INTERVAL '1 week' limits results to those in the last week, while the
BETWEEN filter only permits timestamps on 2021-06-09 & 10. Since 2021-06-10 is now well over a week in the past, there are no dates that satisfy both conditions, and you will never get results.
Guessing a bit as to your requirements, but I assume that when you have a
created_at between (a, b) clause, you should remove the
created_at > localtimestamp - INTERVAL '1 week' one.
i found what i want with this sql code :
SELECT sum(inspected) as inspected,sum(rejected) as rejected, linespeed ,to_timestamp((floor((extract('epoch' from created_at) / 3600 )) * 3600 )) as interval_alias FROM public."MULTI_M22" where created_at BETWEEN '2021-06-09 13:28:00.115348+03' AND '2021-06-09 16:32:00.115348+03' GROUP BY interval_alias, linespeed order by interval_alias
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂