0

This is table:

CREATE table point (
         datetime timestamp,
         value float)

I have data in some range of time

7:00 - 7:15:   0   5 : 5

7:15 - 7:30:   -   - : null

7:30 - 7:45:   7   - : 2


INSERT INTO point values ('2011-01-01 07:01:00', 0);
INSERT INTO point values ('2011-01-01 07:14:00', 5);
INSERT INTO point values ('2011-01-01 07:31:00', 7);

This is the the query:

SELECT date_trunc('minute', datetime) - ((EXTRACT(MINUTE FROM 
      datetime)::int) % 15) * interval '1 minute', 
    MAX(value) - MIN(value)
    from point
    where datetime BETWEEN '2011-01-01 07:00:00' and '2011-01-01 07:45:00'
    group by 1

This is the current result:

2011-01-01 07:00:00 |        5
2011-01-01 07:30:00 |        0

My expect result is:

2011-01-01 07:00:00 |        5
2011-01-01 07:30:00 |        2

The second result I want is 7 - (5 - 0) = 2. If previous time also has only one value, it also should be 7 - (5 - 0) = 2.

  • Please it is not clear, can you add a sample using some fiddle like dbfiddle with a create table and insert statement? – McNets Dec 1 '17 at 13:12
  • I edited the post – Viet Dao Dec 1 '17 at 14:09
  • Stop and delete what you've got. Show us the actual data. Preferably with INSERT and CREATE TABLE statements. Then show us what you want. – Evan Carroll Dec 1 '17 at 18:31
  • What if the previous range also has only 1 value? Your question remains ambiguous. Table definitions and Postgres version are minimum requirements for this. – Erwin Brandstetter Dec 2 '17 at 1:37
  • I'm very sorry for my unclear post. I added the table, insert and results. – Viet Dao Dec 2 '17 at 9:44
1

If I understand correctly ...

Use a subquery and compute the value depending on the count of rows.
Then get the final result using the window function lag():

SELECT time_interval
     , CASE WHEN ct > 1 THEN span ELSE span - lag(span) OVER (ORDER BY time_interval) END AS span
FROM  (
   SELECT date_trunc('minute', datetime) - ((EXTRACT(MINUTE FROM datetime)::int) % 15) * interval '1 minute' AS time_interval
        , CASE WHEN count(*) > 1 THEN MAX(value) - MIN(value) ELSE MAX(value) END AS span
        , count(*) AS ct
   FROM   point
   WHERE  datetime BETWEEN '2011-01-01 07:00:00' and '2011-01-01 07:45:00'
   GROUP  BY 1
   ) sub

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.