public inbox for [email protected]
help / color / mirror / Atom feedElectricity bill
5+ messages / 4 participants
[nested] [flat]
* Electricity bill
@ 2022-06-08 01:39 Theodore M Rolle, Jr. <[email protected]>
2022-06-08 01:42 ` Re: Electricity bill Jonathan Katz <[email protected]>
2022-06-08 01:47 ` Re: Electricity bill David G. Johnston <[email protected]>
0 siblings, 2 replies; 5+ messages in thread
From: Theodore M Rolle, Jr. @ 2022-06-08 01:39 UTC (permalink / raw)
To: pgsql-sql <[email protected]>
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...
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Electricity bill
2022-06-08 01:39 Electricity bill Theodore M Rolle, Jr. <[email protected]>
@ 2022-06-08 01:42 ` Jonathan Katz <[email protected]>
2022-06-08 01:48 ` Fwd: Electricity bill Theodore M Rolle, Jr. <[email protected]>
1 sibling, 1 reply; 5+ messages in thread
From: Jonathan Katz @ 2022-06-08 01:42 UTC (permalink / raw)
To: [email protected]; +Cc: pgsql-sql <[email protected]>
> On Jun 7, 2022, at 9:39 PM, Theodore M Rolle, Jr. <[email protected]> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Fwd: Electricity bill
2022-06-08 01:39 Electricity bill Theodore M Rolle, Jr. <[email protected]>
2022-06-08 01:42 ` Re: Electricity bill Jonathan Katz <[email protected]>
@ 2022-06-08 01:48 ` Theodore M Rolle, Jr. <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Theodore M Rolle, Jr. @ 2022-06-08 01:48 UTC (permalink / raw)
To: pgsql-sql <[email protected]>
Will this compute the interval in days?
---------- Forwarded message ---------
From: Theodore M Rolle, Jr. <[email protected]>
Date: Tue, Jun 7, 2022, 21:46
Subject: Re: Electricity bill
To: Jonathan Katz <[email protected]>
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 protected]>
wrote:
>
> > On Jun 7, 2022, at 9:39 PM, Theodore M Rolle, Jr. <[email protected]>
> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Electricity bill
2022-06-08 01:39 Electricity bill Theodore M Rolle, Jr. <[email protected]>
@ 2022-06-08 01:47 ` David G. Johnston <[email protected]>
2022-06-08 14:15 ` Re: Electricity bill Joe Conway <[email protected]>
1 sibling, 1 reply; 5+ messages in thread
From: David G. Johnston @ 2022-06-08 01:47 UTC (permalink / raw)
To: [email protected]; +Cc: pgsql-sql <[email protected]>
On Tue, Jun 7, 2022 at 6:39 PM Theodore M Rolle, Jr. <[email protected]>
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Electricity bill
2022-06-08 01:39 Electricity bill Theodore M Rolle, Jr. <[email protected]>
2022-06-08 01:47 ` Re: Electricity bill David G. Johnston <[email protected]>
@ 2022-06-08 14:15 ` Joe Conway <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Joe Conway @ 2022-06-08 14:15 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; [email protected]; +Cc: pgsql-sql <[email protected]>
On 6/7/22 21:47, David G. Johnston wrote:
> On Tue, Jun 7, 2022 at 6:39 PM Theodore M Rolle, Jr. <[email protected]
> <mailto:[email protected]>> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2022-06-08 14:15 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-06-08 01:39 Electricity bill Theodore M Rolle, Jr. <[email protected]>
2022-06-08 01:42 ` Jonathan Katz <[email protected]>
2022-06-08 01:48 ` Theodore M Rolle, Jr. <[email protected]>
2022-06-08 01:47 ` David G. Johnston <[email protected]>
2022-06-08 14:15 ` Joe Conway <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox