Subject: Electricity bill

From: "Theodore M Rolle, Jr." <[email-masked]>
Date: Tue, 7 Jun 2022 21:39:09 -0400

I'm putting in YYYY-MM-DD dates of electricity bills and would like to not
have starting and ending dates in the same row. Only ending date.
Will someone show me the SQL to compute the months’ usage? This requires
retrieving two rows to compute the number of days...

---

From: Jonathan Katz <[email-masked]>
Date: Tue, 7 Jun 2022 21:42:27 -0400

> On Jun 7, 2022, at 9:39 PM, Theodore M Rolle, Jr. <[email-masked]> wrote:
> 
> I'm putting in YYYY-MM-DD dates of electricity bills and would like to not have starting and ending dates in the same row. Only ending date.
> Will someone show me the SQL to compute the months’ usage? This requires retrieving two rows to compute the number of days…

Based on your description, what I can offer right now is:

	SELECT date_trunc(‘month’, date_col), sum(val_col);

Jonathan

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 7 Jun 2022 18:47:17 -0700

> retrieving two rows to compute the number of days...

You can use a window function called lead (or lag) to retrieve a value from
the next (previous) row and associate it with the current row.

David J.

---

From: "Theodore M Rolle, Jr." <[email-masked]>
Date: Tue, 7 Jun 2022 21:48:59 -0400

Will this compute the interval in days?

---------- Forwarded message ---------
From: Theodore M Rolle, Jr. <[email-masked]>
Date: Tue, Jun 7, 2022, 21:46
Subject: Re: Electricity bill
To: Jonathan Katz <[email-masked]>

Will this get the current and the last months' dates to compute the
interval in days?

On Tue, Jun 7, 2022, 21:42 Jonathan Katz <[email-masked]>
wrote:

> > I'm putting in YYYY-MM-DD dates of electricity bills and would like to
> not have starting and ending dates in the same row. Only ending date.
> retrieving two rows to compute the number of days…
>

---

From: Joe Conway <[email-masked]>
Date: Wed, 8 Jun 2022 10:15:58 -0400

On 6/7/22 21:47, David G. Johnston wrote:
> On Tue, Jun 7, 2022 at 6:39 PM Theodore M Rolle, Jr. <[email-masked] 
> <mailto:[email-masked]>> wrote:
> 
>     I'm putting in YYYY-MM-DD dates of electricity bills and would like
>     to not have starting and ending dates in the same row. Only ending date.
>     Will someone show me the SQL to compute the months’ usage? This
>     requires retrieving two rows to compute the number of days...
> 
> You can use a window function called lead (or lag) to retrieve a value 
> from the next (previous) row and associate it with the current row.

Another way that might work for you is interval math and ranges, e.g.:

select enddt, month, year from bill;
              enddt             | month | year
-------------------------------+-------+------
  2021-12-31 23:59:59.999999-05 | dec   | 2021
  2022-01-31 23:59:59.999999-05 | jan   | 2022
  2022-02-28 23:59:59.999999-05 | feb   | 2022
  2022-03-31 23:59:59.999999-04 | mar   | 2022
  2022-04-30 23:59:59.999999-04 | apr   | 2022
  2022-05-31 23:59:59.999999-04 | may   | 2022
  2022-06-30 23:59:59.999999-04 | jun   | 2022
  2022-07-31 23:59:59.999999-04 | jul   | 2022
  2022-08-31 23:59:59.999999-04 | aug   | 2022
  2022-09-30 23:59:59.999999-04 | sep   | 2022
  2022-10-31 23:59:59.999999-04 | oct   | 2022
  2022-11-30 23:59:59.999999-05 | nov   | 2022
  2022-12-31 23:59:59.999999-05 | dec   | 2022
(13 rows)

WITH tsr (tr, month, year) AS
(
  SELECT
   tstzrange(b.enddt - '1 month'::interval,
             b.enddt,
             '(]') AS tr,
   b.month,
   b.year
  FROM
   bill b
)
SELECT tr, month, year
FROM tsr
WHERE now() <@ tr;
-[ RECORD 1 ]----------------------------------------------
tr    | ["2022-05-30 00:00:00-04","2022-06-30 00:00:00-04")
month | jun
year  | 2022

Adjust the open/closed bounds to suit.

HTH,

-- 
Joe Conway
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com