get count of records every x time interval between two dates return no records

All we need is an easy explanation of the problem, so here it is.

i have a table with the following data

inspected rejected created_at
423 55 2021-06-09 13:28:00.115348+03
121 22 2021-06-09 13:29:00.115348+03
342 22 2021-06-09 13:30:00.115348+03
546 21 2021-06-09 13:31:00.115348+03
122 22 2021-06-09 13:32:00.115348+03
312 25 2021-06-09 13:33:00.115348+03
1234 12 2021-06-09 13:34:00.115348+03
1331 63 2021-06-09 13:35:00.115348+03

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

here is a sample data what i want is to sum the inspected and rejected and show only one of the values of the linespeed column but grouped into changeable interval ( i will change using javascript and in this sample it’s 5 min)

interval inspected rejected linespeed
‘2021-06-09 13:35:00.0000+03’ 3242 23 333
‘2021-06-09 13:40:00.0000+03’ 2424 33 333

edit —-

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.

Method 1

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

The 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.

Method 2

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

thanks

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply