Thread: Electricity bill
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...
> On Jun 7, 2022, at 9:39 PM, Theodore M Rolle, Jr. <stercor@gmail.com> 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
On Tue, Jun 7, 2022 at 6:39 PM Theodore M Rolle, Jr. <stercor@gmail.com> 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.
David J.
Will this compute the interval in days?
---------- Forwarded message ---------
From: Theodore M Rolle, Jr. <stercor@gmail.com>
Date: Tue, Jun 7, 2022, 21:46
Subject: Re: Electricity bill
To: Jonathan Katz <jonathan.katz@excoventures.com>
From: Theodore M Rolle, Jr. <stercor@gmail.com>
Date: Tue, Jun 7, 2022, 21:46
Subject: Re: Electricity bill
To: Jonathan Katz <jonathan.katz@excoventures.com>
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 <jonathan.katz@excoventures.com> wrote:
> On Jun 7, 2022, at 9:39 PM, Theodore M Rolle, Jr. <stercor@gmail.com> 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
On 6/7/22 21:47, David G. Johnston wrote: > On Tue, Jun 7, 2022 at 6:39 PM Theodore M Rolle, Jr. <stercor@gmail.com > <mailto:stercor@gmail.com>> 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