Hi, I need to display data from a table (vw_tracking_resource_events), but only 14 days of data for-CodePudding

I use PostgreSQL

I can run a single query per day, but it will take a long time to go through every day.

The "zone" and "reader" also changes, so to run single queries every time will keep me up until late. If at best I can only change the "reader" and "zone" every time it would help. The main "PAIN" I have, is to change the dates every time. It will be from 2022 11 18 18:00 to 2022 12 01 19:00.

P.S - I'm new to SQL, please be gentle :)

My current query:

select * from vw_tracking_resource_events
where "when_enter_dt_timezone" between '2022 11 18 18:00:00' and '2022 11 18 19:00:00'
and "zone" = '085 Level'
and "site" = 'MK'
and "reader" = 'RV Shaft'
and "group" = 'Lamp'

CodePudding user response：

You can try something like this to get records for the last 14 days between 6:00 p.m. and 7:00 p.m.

select * from vw_tracking_resource_events 
   where when_enter_dt_timezone > current_date - interval '14' day and
extract(hour from cast(when_enter_dt_timezone as timestamp)) between 18 and 19;

Demo in sqldaddy.io

CodePudding user response：

If you cast your field to separate and compare the date part and the time part to desired ranges, it becomes super easy:

WHERE when_enter_dt_timezone BETWEEN '2022-11-18' AND '2022-12-01T23:59:59.999'
AND when_enter_dt_timezone::time BETWEEN '18:00' AND '19:00'

Edit:
@Stefanov.sm makes a very good point regarding the casting of the timestamp to type date (1st criterion above) if an index can be used to retrieve data.
I corrected the query to take his remark.

Disclaimer: With when_enter_dt_timezone::date BETWEEN ... AND 2022-12-01, you include e.g. 2021-12-01T18:30.
Without the cast, the upper bound 2022-12-01 is implicitly set to midnight (morning); you will either have to change the upper bound to 2022-12-02 (which @Stefanov.sm suggested and works very well since you have a condition on the time anyway) or set your upper bound to 2022-12-01T23:59:59.999 (which is what I did above, although only to draw your attention to this specific issue).