Question 90703 in PostgreSQL Question posted in
The official documentation can be found here.

Hi, I need to display data from a table (vw_tracking_resource_events), but only 14 days of data for a certain time (18:00 to 19:00) – Postgresql

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'
Tags:

2

Answers


  1. 0

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

    Login or Signup to reply.
  2. 0

    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
when_enter_dt_timezone::time between time '18:00' AND time '19:00'

    Demo in sqldaddy.io

    Modified using @Atmo notes
    and @a_horse_with_no_name

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search