0

I have a table with the following data:

id    date_changed                  color_start         color_end
-------------------------------------------------------------------

1     2020-05-27 16:33:52.000       green                yellow
1     2020-06-11 20:12:18.000       yellow               red
1     2020-06-11 20:20:58.000       red                  green
2     2021-03-03 14:31:44.000       yellow               red
2     2020-08-06 14:59:21.000       green                yellow              
3     2021-04-28 12:36:45.000       green                red
...

Fore example, item with id #2 went from green to yellow on 2020-08-06 14:59:21 and then on 2021-03-03 14:31:44 it went from yellow to read. I need to count how many items were in green, yellow, red state between two time ranges.

I tried the following query by doing some research to basically list events or everyday for the past year, but it is not really what I want.

SELECT d.date, items.id,
count(CASE WHEN items.color_end = 'yellow' THEN 1 ELSE null END) as yellow_count,
count(CASE WHEN items.color_end = 'green' THEN 1 ELSE null END) as green_count,
count(CASE WHEN items.color_end = 'red' THEN 1 ELSE null END) as red_count,
count(CASE WHEN items.color_end = 'yellow' THEN 1 ELSE null end) + 
count(CASE WHEN items.color_end = 'green' THEN 1 ELSE null END) + 
count(CASE WHEN items.color_end = 'red' THEN 1 ELSE null END) as total_count
FROM (SELECT to_char(date_trunc('day', (current_date - offs)), 'YYYY-MM-DD') AS date 
      FROM generate_series(0, 365, 1) AS offs
     ) d LEFT OUTER JOIN
     events items
     ON d.date = to_char(date_trunc('day', item.date_changed), 'YYYY-MM-DD')
GROUP BY d.date, items.id;
1
  • can you paste what you really want? Dec 3, 2021 at 4:59

1 Answer 1

0

Your problem here is that your data model is flawed.

You have 6 records:

id    date_changed                  color_start         color_end
-------------------------------------------------------------------

1     2020-05-27 16:33:52.000       green                yellow
1     2020-06-11 20:12:18.000       yellow               red
1     2020-06-11 20:20:58.000       red                  green
2     2021-03-03 14:31:44.000       yellow               red
2     2020-08-06 14:59:21.000       green                yellow              
3     2021-04-28 12:36:45.000       green                red

You should have 9 records. So, your record structure should be this (example for id = 1 only):

id    date_from               date_to                        status

1   -infinity,                2020-05-27 16:33:52+01         green
1   2020-05-27 16:33:52+01,   2020-06-11 20:12:18+01        yellow
1   2020-06-11 20:12:18+01,   2020-06-11 20:20:58+01           red
1   2020-06-11 20:20:58+01    +infinity                      green

You are trying to record two states in one record in two different fields. So, there are two possible refactoring solutions:

  • revise your schema (see the fiddle here). Even if you don't (or can't) change your underlying tables, you can use a Common Table Expression (CTE) to refactor your current data and then query off that. A permanent refactoring would be vastly preferable!

  • revised schema using the TSTZRANGE data type (see fiddle here).

You might also like to consider what I term "auxiliary" solutions - i.e. roll-your-own triggers or possibly Temporal Tables - currently only available as a (non-contrib) PostgreSQL extension. If I had any criticisms of PostgreSQL, one of them would be that they haven't implemented this natively (yet)!

We start with the tables/data as is:

--
--  Original table design from the OP
--

CREATE TABLE test
(
  id INT NOT NULL,
  dc TIMESTAMPTZ(0) NOT NULL,  -- all times are xx:yy:zz.000, so use a precision of 0
  cs TEXT NOT NULL,
  ce TEXT NOT NULL,
  
  UNIQUE (id, dc),
  CHECK (cs != ce)
);

the records:

--
--  OP's data
--

INSERT INTO test VALUES
(1, '2020-05-27 16:33:52', 'green',  'yellow'),
(1, '2020-06-11 20:12:18', 'yellow', 'red'),
(1, '2020-06-11 20:20:58', 'red',    'green'),

(2, '2020-08-06 14:59:21', 'green',  'yellow'),              
(2, '2021-03-03 14:31:44', 'yellow', 'red'),

(3, '2021-04-28 12:36:45', 'green',  'red');

1st solution - two TIMESTAMPTZ fields per record (see fiddle):

We start by using the LAG() and LEAD() PostgreSQL window functions. Window functions are incredibly powerful and well worth learning. They will repay any effort spent studying them many times over.

--
--  "Foundation" query - using this as a Common Table Expression, we can refactor our
--  data - or better yet, refactor our base tables in our system.
--

SELECT
  id,
  COALESCE(LAG(dc)  OVER (PARTITION BY id ORDER BY dc), '-INFINITY') AS lag_dc,
  cs,
  dc,
  COALESCE(LEAD(dc) OVER (PARTITION BY id ORDER BY dc),  'INFINITY') AS lead_dc,
  ce
FROM
  test;

Result (better viewed on the fiddle):

id        lag_dc           cs            dc               lead_dc             ce
1   -infinity              green    2020-05-27 16:33:52+01  2020-06-11 20:12:18+01  yellow
1   2020-05-27 16:33:52+01 yellow   2020-06-11 20:12:18+01  2020-06-11 20:20:58+01             red
1   2020-06-11 20:12:18+01 red      2020-06-11 20:20:58+01  infinity    green
2   -infinity              green    2020-08-06 14:59:21+01  2021-03-03 14:31:44+00  yellow
2   2020-08-06 14:59:21+01 yellow   2021-03-03 14:31:44+00  infinity    red
3   -infinity              green    2021-04-28 12:36:45+01  infinity    red

I won't reproduce every query on the fiddle - here is the first:

--
-- We get the first record of each set (by id) - from '-INFINITY' to the first 
-- date_changed (dc)
--

WITH cte1 AS
(
  SELECT
    id,
    COALESCE(LAG(dc)  OVER (PARTITION BY id ORDER BY dc), '-INFINITY') AS lag_dc,
    cs,
    dc,
    COALESCE(LEAD(dc) OVER (PARTITION BY id ORDER BY dc),  'INFINITY') AS lead_dc,
    ce
  FROM
    test
)
SELECT
  c1.id, c1.lag_dc AS df, c1.dc AS dt, c1.cs
FROM cte1 c1 WHERE lag_dc = '-INFINITY';

Result:

id      df              dt               cs
1   -infinity   2020-05-27 16:33:52+01  green
2   -infinity   2020-08-06 14:59:21+01  green
3   -infinity   2021-04-28 12:36:45+01  green

Then we UNION three of these queries:

  • the first record in each id set,
  • the middle records of each group,
  • last record per id.

as follows:

--
-- We now obtain the union of all 3 sets and we have our result!
--

WITH cte AS
(
  SELECT
    id,
    COALESCE(LAG(dc)  OVER (PARTITION BY id ORDER BY dc), '-INFINITY') AS lag_dc,
    cs,
    dc,
    COALESCE(LEAD(dc) OVER (PARTITION BY id ORDER BY dc),  'INFINITY') AS lead_dc,
    ce
  FROM
    test
)
SELECT 
  c1.id, 
  c1.lag_dc AS "Date from:", 
  c1.dc     AS "Date to:", 
  c1.cs     AS "Colour"  
FROM cte c1 
WHERE lag_dc = '-INFINITY'  -- first records
UNION ALL
SELECT  
  c2.id, c2.dc, c2.lead_dc, c2.ce FROM cte c2 WHERE lead_dc = 'INFINITY'  -- last records
UNION ALL
SELECT 
  c3.id, c3.dc, c3.lead_dc, c3.ce FROM cte c3  WHERE c3.lead_dc != 'INFINITY'  -- middle records
ORDER BY 1, 2;

Result:

id  Date from:               Date to:               Colour
 1  -infinity                2020-05-27 16:33:52+01 green
 1  2020-05-27 16:33:52+01   2020-06-11 20:12:18+01 yellow
 1  2020-06-11 20:12:18+01   2020-06-11 20:20:58+01 red
 1  2020-06-11 20:20:58+01  infinity                green
 2  -infinity                2020-08-06 14:59:21+01 green
 2  2020-08-06 14:59:21+01   2021-03-03 14:31:44+00 yellow
 2  2021-03-03 14:31:44+00   infinity               red
 3  -infinity                2021-04-28 12:36:45+01 green
 3  2021-04-28 12:36:45+01   infinity               red

This makes querying the data relatively simple - two sample queries.

1st Sample Query: Changes of status (colour) in 2021:

--
-- Records where the beginning and the end of the range falls 
-- anywhere >= 2021:01:01 00:00:00
--
-- It's bascially a record of any changes in status in 2021!
--

SELECT
  *
FROM
  test_rs
WHERE ts_from >= '2021-01-01 00:00:00' ANd ts_to >= '2021-01-01 00:00:00'
ORDER BY id, ts_from;

Result:

id  ts_from                 ts_to       colour
2   2021-03-03 14:31:44+00  infinity       red
3   2021-04-28 12:36:45+01  infinity       red

2nd Sample Query: Count of status at a point in time (New Year's Day 2021):

--
--  Status counts at exactly New Year, 2021 - we know that at the point, we had two
--  entities with status green and 1 with status yellow
--

SELECT 
  colour, COUNT(colour)
FROM
  test_rs
WHERE ts_from <= '2021-01-01 00:00:00' AND ts_to >= '2021-01-01 00:00:00'
GROUP BY colour
ORDER BY colour;

Result:

colour    count
green         2
yellow        1

2nd solution - a single TSTZRANGE field per record (see fiddle):

From the manual, we have inclusive and exclusive bounds and infinite (or unbounded - perhaps a better term) bounds - i.e. [ or ] are inclusive bounds and ( and ) are exclusive bounds. Also, [, or (, is unbounded upper and unbounded for the closing brackets (,] or ,)) .

Refactoring query for Time Stamp ranges:

I won't go through all of the queries to obtain the end result - it's a similar process to the one used above. Only the final query is displayed:

WITH cte AS
(

--
--  We don't need COALESCE in this case, since the range treats 'NULL' as -INFINITY
--  or +INFIITY depending on whether it's at the beginning or end of the range.
--

  SELECT
    id,
    LAG(dc)  OVER (PARTITION BY id ORDER BY dc) AS lag_dc,
    cs,
    dc,
    LEAD(dc) OVER (PARTITION BY id ORDER BY dc) AS lead_dc,
    ce
  FROM
    test
)
SELECT
  c.id, 
  TSTZRANGE(c.lag_dc, c.dc, '[)') AS "Date from:/Date to:", 
  c.cs AS "Colour"
FROM cte c WHERE c.lag_dc IS NULL
UNION ALL
SELECT  c.id, TSTZRANGE(c.dc, c.lead_dc, '[)'), c.ce 
FROM cte c WHERE lead_dc IS NULL
UNION ALL
SELECT c.id, TSTZRANGE(c.dc, c.lead_dc, '[)'), c.ce 
FROM cte c 
WHERE c.lead_dc != 'INFINITY'
ORDER BY 1, 2;

Result:

id     Date from:/Date to:                                Colour
1   (,"2020-05-27 16:33:52+01")                            green
1   ["2020-05-27 16:33:52+01","2020-06-11 20:12:18+01")   yellow
1   ["2020-06-11 20:12:18+01","2020-06-11 20:20:58+01")      red
1   ["2020-06-11 20:20:58+01",)                            green
2   (,"2020-08-06 14:59:21+01")                            green
2   ["2020-08-06 14:59:21+01","2021-03-03 14:31:44+00")   yellow
2   ["2021-03-03 14:31:44+00",)                              red
3   (,"2021-04-28 12:36:45+01")                            green
3   ["2021-04-28 12:36:45+01",)                              red

1st sample query: Change of status any time after the beginning of 2021.

--
-- Records where the beginning and the end of the range falls 
-- anywhere >= 2021:01:01 00:00:00
--
-- It's bascially a record of any changes in status in 2021!
--

SELECT * FROM test_rs
WHERE 
LOWER(df_dt) > '2021-01-01 00:00:00' AND
df_dt && TSTZRANGE('2021-01-01 00:00:00'::TIMESTAMPTZ, NULL, '[)');

Result:

id        df_dt                   colour
2   ["2021-03-03 14:31:44+00",)      red
3   ["2021-04-28 12:36:45+01",)      red

We can see that this is equivalent to the first sample query with the range field in place of the two timestamps.

The WHERE clause is worth looking at: LOWER(df_dt) and WHERE trb && TSTZRANGE('2021-01-01 00:00:00'::TIMESTAMPTZ, NULL, '[)'); - it makes use of the && (overlaps) operator (see the manual - I also found this post helpful).

So, we can see that any time range that overlaps with anytime from 2021-01-01 00:00:00 onwards (...MPTZ, NULL, '[)'); is picked up - note the NULL in constructing the TSTZRANGE value(s).

2nd query: colour count at exactly the beginning of 2021.

--
--  Status counts at exactly New Year, 2021 - we know that at the point, we had two
--  entities with status green and 1 with status yellow
--

SELECT colour, COUNT(colour) FROM test_rs
WHERE df_dt && TSTZRANGE('2021-01-01 00:00:00', '2021-01-01 00:00:00', '[]')
GROUP BY colour;

Result (same as for 2nd sample query with two timestamps):

colour    count
 green        2
yellow        1

Auxiliary solutions:

There are other ways of refactoring your schema (and or app code) to track changes in the database, in particular for tables referencing timestamp fields - a complete discussion of these is beyond the scope of this answer, but you might like to consider the following:

  • roll your own using triggers or

  • install Vlad Arkhipov's Temporal Table solution. I have used this in a POC and it appears to work quite well, but has nothing like the functionality of, say, MariaDB's solution. This involves compiling and installing a C based extension.

  • install Vik Fearing's (a PostgreSQL Major Contributor) temporal table extension (also in C). I haven't used it, but the fact that the guy is a major contributor speaks for itself. As of the time of writing, appears to be the most up to date!

  • Near Form's temporal_tables functionality, which according to the github link is: a temporal_tables extension in PL/pgSQL, without the need for external c extension. The goal is to be able to use it on AWS RDS and other hosted solutions, where using custom extensions or c functions is not an option. Sounds interesting, but I haven't tried it so can't comment.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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