pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedgenerated dates from record dates - suggestions
6+ messages / 3 participants
[nested] [flat]
* generated dates from record dates - suggestions
@ 2012-08-20 12:17 Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 3 replies; 6+ messages in thread
From: Gary Stainburn @ 2012-08-20 12:17 UTC (permalink / raw)
To: pgsql-sql
Hi folks.
I've got a table with three dates which are populated from an external source.
I then want to have a view with two calculated dates in it, e.g.
if date_1 is null and date_2 is null then date_a=NULL
if date_1 is not null and date 2 is null then date_a=date_1+'90 days'
if date_1 > date3 rhen date_b=date1 else date_b=date2
etc.
What's the best way to do this?
I know it's a quite open question but I'm interested to hear different
responses
--
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.ringways.co.uk
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: generated dates from record dates - suggestions
@ 2012-08-20 14:19 Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
2 siblings, 0 replies; 6+ messages in thread
From: Oliveiros d'Azevedo Cristina @ 2012-08-20 14:19 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; pgsql-sql
With a CASE ... WHEN statement?
Best,
Oliver
----- Original Message -----
From: "Gary Stainburn" <gary.stainburn@ringways.co.uk>
To: <pgsql-sql@postgresql.org>
Sent: Monday, August 20, 2012 1:17 PM
Subject: [SQL] generated dates from record dates - suggestions
> Hi folks.
>
> I've got a table with three dates which are populated from an external
> source.
> I then want to have a view with two calculated dates in it, e.g.
>
> if date_1 is null and date_2 is null then date_a=NULL
> if date_1 is not null and date 2 is null then date_a=date_1+'90 days'
> if date_1 > date3 rhen date_b=date1 else date_b=date2
> etc.
>
> What's the best way to do this?
>
> I know it's a quite open question but I'm interested to hear different
> responses
>
>
> --
> Gary Stainburn
> Group I.T. Manager
> Ringways Garages
> http://www.ringways.co.uk
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: generated dates from record dates - suggestions
@ 2012-08-21 10:31 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
2 siblings, 1 reply; 6+ messages in thread
From: Gary Stainburn @ 2012-08-21 10:31 UTC (permalink / raw)
To: pgsql-sql
On Tuesday 21 August 2012 02:01:55 Johnny Winn wrote:
> I would define a function and use it to abstract the expected behavior. It
> would appear that there are several conditions so this abstraction would
> provide for both usability and maintainability.
>
> - Johnny
Johnny,
This was what I was wondering, and was looking for suggestions / best
practices on how to do this.
I had first thought of embedding case/when statements in the view but it could
easily become unweildy
--
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.ringways.co.uk
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: generated dates from record dates - suggestions
@ 2012-08-21 11:14 Jasen Betts <jasen@xnet.co.nz>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
2 siblings, 1 reply; 6+ messages in thread
From: Jasen Betts @ 2012-08-21 11:14 UTC (permalink / raw)
To: pgsql-sql
On 2012-08-20, Gary Stainburn <gary.stainburn@ringways.co.uk> wrote:
> Hi folks.
>
> I've got a table with three dates which are populated from an external source.
> I then want to have a view with two calculated dates in it, e.g.
>
> if date_1 is null and date_2 is null then date_a=NULL
> if date_1 is not null and date 2 is null then date_a=date_1+'90 days'
> if date_1 > date3 rhen date_b=date1 else date_b=date2
> etc.
>
> What's the best way to do this?
CASE looks good.
> I know it's a quite open question but I'm interested to hear different
> responses
"date_b" on line three, is that a typo? if not how am i to interpret
it?
--
⚂⚃ 100% natural
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: generated dates from record dates - suggestions
@ 2012-08-21 12:15 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: Jasen Betts <jasen@xnet.co.nz>
0 siblings, 0 replies; 6+ messages in thread
From: Gary Stainburn @ 2012-08-21 12:15 UTC (permalink / raw)
To: pgsql-sql
On Tuesday 21 August 2012 12:14:53 Jasen Betts wrote:
> > What's the best way to do this?
>
> CASE looks good.
>
> > I know it's a quite open question but I'm interested to hear different
> > responses
>
> "date_b" on line three, is that a typo? if not how am i to interpret
> it?
The table contains date_1, date_2 and date_3. The resulting view needs to
contain date_1, date_2, date_3, date_a and date_b where date_a and date_b are
calculated based on the first three (plus a text field).
--
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.ringways.co.uk
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: generated dates from record dates - suggestions
@ 2012-08-22 10:49 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 0 replies; 6+ messages in thread
From: Gary Stainburn @ 2012-08-22 10:49 UTC (permalink / raw)
To: Johnny Winn <j.winn.v@gmail.com>; +Cc: pgsql-sql
On Tuesday 21 August 2012 13:11:06 Johnny Winn wrote:
> CREATE OR REPLACE FUNCTION get_dates(date, date, date) RETURNS TABLE(date1
> date, date2 date)
> AS $$
> DECLARE
> date_1 DATE := NULL;
> date_2 DATE := NULL;
> BEGIN
>
> -- test your conditions here
>
> RETURN QUERY SELECT date_1::date, date_2::date;
> END;
> $$
> LANGUAGE PLPGSQL;
>
> I hope this helps,
> Johnny
Johnny,
Having gone down the CASE/WHEN route and found it too clumsy I'm now looking
at using this method. I'm just about to start writing the function, but I'm
wondering how I would include this is the select / view .
Gary
--
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.ringways.co.uk
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2012-08-22 10:49 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-08-20 12:17 generated dates from record dates - suggestions Gary Stainburn <gary.stainburn@ringways.co.uk>
2012-08-20 14:19 ` Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2012-08-21 10:31 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2012-08-22 10:49 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2012-08-21 11:14 ` Jasen Betts <jasen@xnet.co.nz>
2012-08-21 12:15 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox