pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedmax (timestamp,timestamp)
5+ messages / 4 participants
[nested] [flat]
* max (timestamp,timestamp)
@ 2006-11-13 13:46 T E Schmitz <mailreg@numerixtechnology.de>
0 siblings, 1 reply; 5+ messages in thread
From: T E Schmitz @ 2006-11-13 13:46 UTC (permalink / raw)
To: pgsql-sql
I tried the following query but the query fails as
"function max (timestamp w. timezone,timestamp w. timezone) does not exist"
SELECT id,
MAX(last_updated,
(SELECT MAX (last_updated) FROM product_category_member WHERE
product_category_member.id = product_category.id))
FROM product_category
product_category.last_updated and product_category_member.last_updated
are timestamps with timezone.
Is there any other way I can produce this result
--
Regards,
Tarlika Elisabeth Schmitz
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: max (timestamp,timestamp)
@ 2006-11-13 14:08 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: T E Schmitz <mailreg@numerixtechnology.de>
0 siblings, 1 reply; 5+ messages in thread
From: A. Kretschmer @ 2006-11-13 14:08 UTC (permalink / raw)
To: pgsql-sql
am Mon, dem 13.11.2006, um 13:46:00 +0000 mailte T E Schmitz folgendes:
> I tried the following query but the query fails as
> "function max (timestamp w. timezone,timestamp w. timezone) does not exist"
>
> SELECT id,
>
> MAX(last_updated,
> (SELECT MAX (last_updated) FROM product_category_member WHERE
> product_category_member.id = product_category.id))
>
> FROM product_category
>
>
> product_category.last_updated and product_category_member.last_updated
> are timestamps with timezone.
Really, there are no such function. Perhaps this can help you:
SELECT id, MAX(product_category.last_updated),
MAX(product_category_member.last_updated) from product_category,
product_category_member WHERE product_category_member.id =
product_category.id;
**untested**
Your fault is that there are no max(timestamp,timestamp) - funktion and
i think, you should read more about JOINs.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: max (timestamp,timestamp)
@ 2006-11-13 14:29 imad <immaad@gmail.com>
parent: A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 1 reply; 5+ messages in thread
From: imad @ 2006-11-13 14:29 UTC (permalink / raw)
To: A. Kretschmer <andreas.kretschmer@schollglas.com>; +Cc: pgsql-sql
max (timestamptz, timestamptz) does not exist already. You need to
create a simple function in PLpgSQL something like
if a > b
return a;
else
return b;
Even an sql function will do the job here using case statement.
--Imad
www.EntepriseDB.com
On 11/13/06, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote:
> am Mon, dem 13.11.2006, um 13:46:00 +0000 mailte T E Schmitz folgendes:
> > I tried the following query but the query fails as
> > "function max (timestamp w. timezone,timestamp w. timezone) does not exist"
> >
> > SELECT id,
> >
> > MAX(last_updated,
> > (SELECT MAX (last_updated) FROM product_category_member WHERE
> > product_category_member.id = product_category.id))
> >
> > FROM product_category
> >
> >
> > product_category.last_updated and product_category_member.last_updated
> > are timestamps with timezone.
>
> Really, there are no such function. Perhaps this can help you:
>
> SELECT id, MAX(product_category.last_updated),
> MAX(product_category_member.last_updated) from product_category,
> product_category_member WHERE product_category_member.id =
> product_category.id;
>
> **untested**
>
>
>
> Your fault is that there are no max(timestamp,timestamp) - funktion and
> i think, you should read more about JOINs.
>
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: max (timestamp,timestamp)
@ 2006-11-13 15:06 Michael Fuhr <mike@fuhr.org>
parent: imad <immaad@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Michael Fuhr @ 2006-11-13 15:06 UTC (permalink / raw)
To: imad <immaad@gmail.com>; +Cc: A. Kretschmer <andreas.kretschmer@schollglas.com>; pgsql-sql
On Mon, Nov 13, 2006 at 07:29:09PM +0500, imad wrote:
> max (timestamptz, timestamptz) does not exist already. You need to
> create a simple function in PLpgSQL something like
>
> if a > b
> return a;
> else
> return b;
Since PostgreSQL 8.1 you can use GREATEST:
test=> SELECT greatest(1, 2);
greatest
----------
2
(1 row)
test=> SELECT greatest(2, 1);
greatest
----------
2
(1 row)
test=> SELECT greatest(6, 3, 1, 10, 9, 5, 2, 7, 8, 4);
greatest
----------
10
(1 row)
--
Michael Fuhr
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: max (timestamp,timestamp)
@ 2006-11-14 15:02 T E Schmitz <mailreg@numerixtechnology.de>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 5+ messages in thread
From: T E Schmitz @ 2006-11-14 15:02 UTC (permalink / raw)
To: pgsql-sql
Michael Fuhr wrote:
> On Mon, Nov 13, 2006 at 07:29:09PM +0500, imad wrote:
>
>>max (timestamptz, timestamptz) does not exist already. You need to
>>create a simple function in PLpgSQL something like
>>
>>if a > b
>>return a;
>>else
>>return b;
>
>
> Since PostgreSQL 8.1 you can use GREATEST:
>
> test=> SELECT greatest(1, 2);
That'll be a handy feature! Unfortunately, my server is still on 7.4.
Thanks to everyone for the quick responses.
--
Regards,
Tarlika
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-11-14 15:02 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-11-13 13:46 max (timestamp,timestamp) T E Schmitz <mailreg@numerixtechnology.de>
2006-11-13 14:08 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-11-13 14:29 ` imad <immaad@gmail.com>
2006-11-13 15:06 ` Michael Fuhr <mike@fuhr.org>
2006-11-14 15:02 ` T E Schmitz <mailreg@numerixtechnology.de>
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