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

Postgresql-Calculate difference between rows based on date and time

I’ve the following values in my Table1 :

Date Time Id Value Unit
2024-01-01 06:00:10 Key_001_C 1557 kWh
2024-01-01 06:00:01 Key_002_01_C 10323 kWh
2024-01-01 06:00:01 Key_002_02_C 8405 kWh
2024-01-01 06:00:01 Key_010_C 122 kWh
2024-01-01 06:00:08 Key_101_01_C 6591 kWh
2024-01-01 06:00:01 Key_101_02_C 6614 kWh
2024-01-01 06:00:01 Key_102_1_C 6441 kWh
2024-01-01 06:00:01 Key_102_2_C 6329 kWh
2024-02-01 06:00:02 Key_001_C 2557 kWh
2024-02-01 06:00:05 Key_002_01_C 9323 kWh
2024-02-01 06:00:10 Key_002_02_C 9405 kWh
2024-02-01 06:00:01 Key_010_C 222 kWh
2024-02-01 06:00:01 Key_101_01_C 7591 kWh
2024-02-01 06:00:01 Key_101_02_C 7614 kWh
2024-02-01 06:00:01 Key_102_1_C 5441 kWh
2024-02-01 06:00:01 Key_102_2_C 7329 kWh

What would be the best way, querying at any time/any day on February (and so on, thus January to December…), to get month differences between Id’s one-by-one, ignoring the seconds in the time ?

Day starts/ends at 06:00am for me (so January starts on Jan, 1st at 6:00am and ends on Feb, 1st at 6:00am).

Of course, I’ve values every hour (to be ignored).

I’m confused about using the lag() function in this case.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Works fine with the following lines (without merging date/time fields) :

    with cte as (
    select
    "Date" as "Date_Ref",
    date_trunc('minute',"Time") as "Time_Ref",
    "Id",
    "Value",
    "Unit"
    from "Table1"
    where date_trunc('minute',"Time") = '06:00:00' and
    (date_trunc('month', "Date") = date_trunc('month', current_date) or date_trunc('month', "Date") = date_trunc('month', current_date - interval '1' month))
    order by 3 asc , 1 asc
    ),
cte2 as (
    select
    "Date_Ref",
    "Time_Ref",
    "Id",
    "Value",
    "Unit",
    lag("Value",1) over (order by 3 asc , 1 asc) as "Previous"
    from cte
    )
    select
    "Id",
    ("Value" - "Previous") as "Last month",
    "Unit"
    from
    cte2
    where "Date_Ref" = date_trunc('month', current_date)::timestamp::date  
    order by 1 asc

  2. 0

    Regarding LAG function we can describe it as

    We use a Lag() function to access previous rows data as per defined offset value. It is a window function available from SQL Server 2012 onwards. It works similar to a Lead function. In the lead function, we access subsequent rows, but in lag function, we access previous rows. It is a useful function in comparing the current row value from the previous row value.

    If you use the following SQL, I believe you will be able to accomplish your goal.

    WITH RankedData AS (
  SELECT
    Date,
    Time,
    Id,
    Value,
    Unit,
    LAG(Date) OVER (ORDER BY Date, Time) AS PrevDate,
    LAG(Time) OVER (ORDER BY Date, Time) AS PrevTime,
    LAG(Id) OVER (ORDER BY Date, Time) AS PrevId
  FROM
    YourTable
)
SELECT
  Date,
  Time,
  Id,
  Value,
  Unit,
  EXTRACT(MONTH FROM 
    CASE WHEN TIME(Time) >= '06:00:00' THEN TIMESTAMP CONCAT(Date, ' ', Time)
         ELSE TIMESTAMP CONCAT(PrevDate, ' ', PrevTime)
    END
  ) AS MonthDiff
FROM
  RankedData;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search