0

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)

4
  • 1
    Hi, and welcome to dba.se! Could you please go to dbfiddle.uk and choose your PostgreSQL server and construct your table (MyTable) and input your data. Then provide us with your sample data above. Most importantly, show us your desired result - I'm having difficulty understanding exactly what it is you want! Put all that information back here - you can put a comment under this with the @ symbol and my handle (no spaces) when you've done this!
    – Vérace
    Jul 2, 2021 at 12:16
  • hi @Vérace i updated my post with sample data and requirements.... thanks in advance
    – j.kherfan
    Jul 3, 2021 at 10:35
  • You haven't produced a fiddle - could you please do that? What's linespeed? We need clues, otherwise, we're flying blind!
    – Vérace
    Jul 4, 2021 at 13:17
  • @Vérace sorry for the inconvenience.. i edited the question and added a fiddle link also added what i need right now as i solved some of my problems
    – j.kherfan
    Jul 4, 2021 at 19:50

2 Answers 2

0

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.

0
0

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

1
  • Don't forget to mark your own answer as correct - it may help others with similar problems!
    – Vérace
    Jul 6, 2021 at 12:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.