public inbox for [email protected]help / color / mirror / Atom feed
Electricity bill 5+ messages / 4 participants [nested] [flat]
* Electricity bill @ 2022-06-08 01:39 Theodore M Rolle, Jr. <[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:42 Jonathan Katz <[email protected]> parent: 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
* Re: Electricity bill @ 2022-06-08 01:47 David G. Johnston <[email protected]> parent: Theodore M Rolle, Jr. <[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
* Fwd: Electricity bill @ 2022-06-08 01:48 Theodore M Rolle, Jr. <[email protected]> parent: Jonathan Katz <[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 14:15 Joe Conway <[email protected]> parent: David G. Johnston <[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