Postgresql – Extract all rows until and including

performancepostgresqlpostgresql-9.4postgresql-performance

I would like to extract all the rows from a table until and including check_type = 'schedule' and checked_at < '2016-09-26', in this situation I don't have a lower bound for checked_at, the lower bound could be the checked_at value of the first row which has the type scheduled, but I don't see how I can get that.

CREATE TABLE project_check_history (
  id serial PRIMARY KEY,
  project_id int4,
  check_type varchar,
  checked_at timestamp
);

INSERT INTO project_check_history(project_id, check_type, checked_at) 
  VALUES 
      (1, 'instant', '2016-09-26'),
      (1, 'instant', '2016-09-25'),
      (1, 'scheduled', '2016-09-24'),
      (1, 'instant', '2016-09-23'),
      (1, 'scheduled', '2016-09-23');
      (1, 'instant', '2016-09-22'),
      (1, 'scheduled', '2016-09-21');

This is part of a bigger question here, but I'm really stuck, and don't see how I can do it.

Expected output it will be:

checked_at
2016-09-26
2016-09-25
2016-09-24

Best Answer

This will do what you want. If there is no row with check_type = 'scheduled' in the specified interval, it will return no rows at all. If you want all the rows instead, it will have to be slightly modified:

WITH find_schedule AS
  ( SELECT created_at
    FROM project_check_history
    WHERE check_type = 'scheduled'
      AND checked_at <= '2016-09-26'
    ORDER BY checked_at DESC
    LIMIT 1
  ) 
SELECT  -- DISTINCT 
    created_at
FROM project_check_history
WHERE checked_at <= '2016-09-26'
  AND checked_at >= (TABLE first_schedule) ;

Related Solutions

Postgresql – How to optimize an ordered date range query with several OR filters (for streaming)

Have you tried making 4 separate queries, and then using union all, so that you can get rid of the or statements?

Example:

select * from my_table
where date >= now() - '1 day'::interval
  and type2 = 'T2A' 
  and type1 = 'T1A'

union all
select * from my_table
where date >= now() - '1 day'::interval
  and type2 = 'T2B' 
  and type1 = 'T1B1'
...

Postgresql – Extract data along a path (lat, long, altitude and time)

First, to make sense of this we convert to use geography points, rather than lat-long. I assume this is in WGS84. We do this because measuring a difference in lat/long doesn't make any sense. As you approach the poles the differences between 3 degrees of lat/long will be nothing compared to the equator.

BEGIN;
  ALTER TABLE flightpath ADD COLUMN geog geography;
  UPDATE flightpath SET geog =
    ST_SetSRID(ST_MakePoint(lon, lat), 4326)::geography;
  ALTER TABLE flightpath DROP COLUMN lon, DROP COLUMN lat;
COMMIT;

BEGIN;
  ALTER TABLE weather ADD COLUMN geog geography;
  UPDATE weather SET geog =
    ST_SetSRID(ST_MakePoint(lon, lat), 4326)::geography;
  ALTER TABLE weather DROP COLUMN lon, DROP COLUMN lat;
COMMIT;

Now you can use ST_DWithin and datetime math.

SELECT
  id,
  f.alt,
  f.time,
  coalesce(w.temp::text, 'no temperature') AS temp
FROM flightpath AS f
LEFT OUTER JOIN weather AS w
  ON (
    (
      w.time - f.time < '3 minutes'::interval      -- diff in time
      AND  f.time - w.time < '3 minutes'::interval
    )
    AND abs(w.alt - f.alt) <= 1000                 -- diff in alt
    AND ST_DWithin(w.geog, f.geog, 1000000)        -- diff in distance (meters)
  )
WHERE id = 1
ORDER BY f.alt;

That outputs something like this..

 id |  alt  |        time         |      temp      
----+-------+---------------------+----------------
  1 | 29000 | 2017-01-01 00:01:05 | no temperature
  1 | 34000 | 2017-01-01 00:01:31 | -9
  1 | 37000 | 2017-01-01 00:01:59 | -11
  1 | 40100 | 2017-01-01 00:02:28 | -15

Best Answer

Related Solutions

Postgresql – How to optimize an ordered date range query with several OR filters (for streaming)

Postgresql – Extract data along a path (lat, long, altitude and time)

Related Question