Postgresql – Combine various temporal criteria to filter rows

datetimepostgresqltemporal-tables

I am trying to query a database table with some filters. I have a form through which a user will create query attributes:

When user will click on search it will pass such values:

 {camera_id: "9879"
, from_date: "2015/05/15"
, to_date: "2016/08/15"
, interval: "120"
, schedule: "{"Monday":["3:0-4:0"],"Tuesday":["1:30-3:30"]
             ,"Wed…":["0:0-3:0"],"Saturday":[],"Sunday":["1:0-3:0"]}"}

interval has one of these names and values:

"All", "0"
"1 Frame Every 1 min", "1"
"1 Frame Every 5 min", "5"
...
"1 Frame Every hour", "60"
...
"1 Frame Every 24 hours", "1440"

For example: "1 Frame every 1 min". If 5 rows in snapshots have been taken within 1 min, the query shall only return the first.

schedule is being sent by using the FullCalendar jquery plugin. Meaning:

Day: Monday,    Time: 03:00 AM to 04:00 AM
Day: Tuesday,   Time: 01:30 AM to 03:30 AM
...

Table snapshots:

  snapshot_id integer
, camera_id   integer
, created_at  timestamp

I want to select rows passing the filters. Temporal filters apply to created_at. I want rows between from_date and to_date, considering the schedule, and only get the first row per interval.

Best Answer

A basic solution to retrieve only the first row per interval would be to

Generate time series using from_date, to_dateand interval with generate_series().
Join to (pre-selected) main table thereby forming 1 group per interval.
Use DISTINCT ON to get the first per group.
- Select first row in each GROUP BY group?

For simpler parameter handling I wrapped the query in a plpgsql function:

CREATE OR REPLACE FUNCTION f_snapshots(_camera_id int
                                     , _from_date timestamp
                                     , _to_date   timestamp
                                     , _interval  interval = '0 min')
  RETURNS SETOF snapshots AS
$func$
BEGIN
IF _interval > '0 min' THEN
   RETURN QUERY
   SELECT DISTINCT ON (i) s.* 
   FROM  (
      SELECT *
      FROM   snapshots
      WHERE  created_at >= _from_date
      AND    created_at <  _to_date    -- exclude upper bound?
      ) s
   JOIN   generate_series(_from_date
                        , _to_date - _interval  -- adjust upper bound
                        , _interval) i ON s.created_at >= i
                                      AND s.created_at <  i + _interval
   ORDER  BY i, created_at;

ELSE  -- no interval, return all rows
   RETURN QUERY
   SELECT *
   FROM   snapshots
   WHERE  created_at >= _from_date
   AND    created_at <  _to_date
   ORDER  BY created_at;
END IF;
END
$func$  LANGUAGE plpgsql STABLE;

Call:

SELECT * FROM f_snapshots(
        _camera_id := '9879'
      , _from_date := '2015-05-15 00:00'
      , _to_date   := '2016-08-15 00:00'
      , _interval  := '120 min');

I ignored the additional filter schedule. Add that yourself. I would extract all intervals from the json document and add OR'ed WHERE conditions. Either use the SQL OVERLAPS operator or the overlap operator && for range types. More:

Also consider my answer to your previous question:

Filter created_at and count distinct minutes

Related Solutions

Postgresql – Interpreting Decision Time (DT) in temporal databases

I don't know if there is necessarily a decision period, but there might be a decision date, separate from the valid period start date and the transaction date, where it makes sense to indicate when exactly a decision was made to make a new entry valid.

In your example the decision to hire Okihara probably had been made at some time prior to '2014-01-01', in which case it might have put certain obligations on the employer even before Okihara's first day on the job, so it made sense to record it separately.

One might presume that the decision date will never be after the transaction date, while the valid date might still be arbitrary.

Always encrypted on Temporal tables

At least it is now possible have encrypted columns on temporal tables:

CREATE TABLE DepartmentHistory   
(    
     DeptID int NOT NULL  
   , DeptName varchar(50) NOT NULL  
   , SysStartTime datetime2 NOT NULL  
    ,[Customer_credit_card_asym] [varbinary](max) NULL
   , SysEndTime datetime2 NOT NULL   
);   
GO   

CREATE NONCLUSTERED INDEX IX_DepartmentHistory_ID_PERIOD_COLUMNS   
   ON DepartmentHistory (SysEndTime, SysStartTime, DeptID);   
GO   
CREATE TABLE Department   
(    
    DeptID int NOT NULL PRIMARY KEY CLUSTERED  
   , DeptName varchar(50) NOT NULL  
   , SysStartTime datetime2  NOT NULL  
    ,Customer_credit_card_asym [varbinary](max) NULL
   , SysEndTime datetime2 NOT NULL           
) ;

CREATE ASYMMETRIC KEY dboAsymKey AUTHORIZATION dbo
WITH ALGORITHM = RSA_2048
ENCRYPTION BY PASSWORD =N'MorilloP@zzwurd';

DECLARE @credit_card1 VARCHAR(100) = '111-1111111-1'

INSERT into Department (DeptId, DeptName, SysStartTime,
Customer_credit_card_asym, SysEndTime)
VALUES
(1, 'Perez' , '2/2/2018 00:00:00.000', EncryptByAsymKey(AsymKey_ID('dboAsymKey'),@credit_card1),
'9999-12-31 23:59:59.9999999')


ALTER TABLE dbo.Department   
   ADD PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])   
ALTER TABLE dbo.Department      
   SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory, DATA_CONSISTENCY_CHECK = ON));

A few years back Msg 13579 was received.

Best Answer

Related Solutions

Postgresql – Interpreting Decision Time (DT) in temporal databases

Always encrypted on Temporal tables

Related Question