Received: from bologna.nettuno.it (bologna.nettuno.it [193.43.2.1] (may be forged)) by hub.org (8.9.3/8.9.3) with ESMTP id IAA78570; Thu, 2 Sep 1999 08:38:13 -0400 (EDT) (envelope-from jose@sferacarta.com) Received: from proxy.sferacarta.com (mail@sfcabop1.nettuno.it [193.207.10.213]) by bologna.nettuno.it (8.9.3/8.9.3/NETTuno 3.3) with ESMTP id OAA20843; Thu, 2 Sep 1999 14:30:34 +0200 (MDT) Received: from rosso.sferacarta.com (sferacarta.com) [10.20.30.5] by proxy.sferacarta.com with esmtp (Exim 2.05 #1 (Debian)) id 11MXmA-0008MX-00; Thu, 2 Sep 1999 14:23:42 +0000 Message-ID: <37CE6DA6.B40F424E@sferacarta.com> Date: Thu, 02 Sep 1999 14:29:26 +0200 From: =?iso-8859-1?Q?Jos=E9?= Soares X-Mailer: Mozilla 4.5 [it] (Win95; I) X-Accept-Language: it MIME-Version: 1.0 To: Esteban Chiner Sanz CC: pgsql-general@postgresql.org, pgsql-sql@postgresql.org Subject: Re: [GENERAL] Max function on Timestamp References: <37CE3592.2558E205@tissat.es> Content-Type: multipart/mixed; boundary="------------DAC176195F4C064AA7CEA545" Questo è un messaggio multi-parte scritto in formato MIME. --------------DAC176195F4C064AA7CEA545 Content-Type: multipart/alternative; boundary="------------368C53E2E25CE33F9079E5BE" --------------368C53E2E25CE33F9079E5BE Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit It works for me (v6.5.1) I have the following MAX aggregates: prova=> \da max aggname|type |description -------+--------+----------- max |abstime | max |date | max |datetime| max |float4 | max |float8 | max |int2 | max |int4 | max |int8 | max |money | max |numeric | max |text | max |timespan| (12 rows) ... prova=> insert into test values (current_timestamp); INSERT 720683 1 prova=> select data from test; data ---------------------- 1999-09-02 16:14:24+02 1999-09-02 16:14:27+02 1999-09-02 16:14:53+02 (3 rows) prova=> select max(data) from test; max --------------------------- 02/09/1999 16:14:53.00 CEST (1 row) If it doesn't work for you try to run the attached script. José Esteban Chiner Sanz ha scritto: > I have a table in my database with a timestamp field and I can't use a > max function on it. You can do it with any other date types (date, > timespan, etc.) but not with timestamp. > Is there any problem on doing it? Why isn't it implemented in PostgreSQL > 6.5.1? What would be the easiest way of doing it? > Thank you in advance, > > Esteban Chiner > > P.D: There aren't also any "less than" or "greater than" operators on > timestamp... > > ------------------------------------------------------------ > Esteban Chiner Sanz mailto (work): echiner@tissat.es > TISSAT > Av. Aragon, 30, 5ª planta Phone: 96 393 9950 > Valencia (SPAIN) > > ************ --------------368C53E2E25CE33F9079E5BE Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit It works for me (v6.5.1)
I have the following MAX aggregates:

prova=> \da max
aggname|type    |description
-------+--------+-----------
max    |abstime |
max    |date    |
max    |datetime|
max    |float4  |
max    |float8  |
max    |int2    |
max    |int4    |
max    |int8    |
max    |money   |
max    |numeric |
max    |text    |
max    |timespan|
(12 rows)

...
prova=> insert into test values (current_timestamp);
INSERT 720683 1
prova=> select data from test;
data
----------------------
1999-09-02 16:14:24+02
1999-09-02 16:14:27+02
1999-09-02 16:14:53+02
(3 rows)

prova=> select max(data) from test;
max
---------------------------
02/09/1999 16:14:53.00 CEST
(1 row)

If it doesn't work for you try to run the attached script.

José
 

Esteban Chiner Sanz ha scritto:

I have a table in my database with a timestamp field and I can't use a
max function on it. You can do it with any other date types (date,
timespan, etc.) but not with timestamp.
Is there any problem on doing it? Why isn't it implemented in PostgreSQL
6.5.1? What would be the easiest way of doing it?
Thank you in advance,

                Esteban Chiner

P.D: There aren't also any "less than" or "greater than" operators on
timestamp...

------------------------------------------------------------
 Esteban Chiner Sanz        mailto (work): echiner@tissat.es
 TISSAT
 Av. Aragon, 30, 5ª planta  Phone: 96 393 9950
 Valencia (SPAIN)

************

--------------368C53E2E25CE33F9079E5BE-- --------------DAC176195F4C064AA7CEA545 Content-Type: text/plain; charset=us-ascii; name="min_max.sql" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="min_max.sql" -- crea le funzioni MIN e MAX per oid, text, char, varchar, timestamp e time. drop function timestampsmaller (timestamp,timestamp); create function timestampsmaller (timestamp,timestamp) returns timestamp as ' begin if $1 > $2 then return $2; else return $1; end if; end; ' language 'plpgsql'; drop aggregate min timestamp; create aggregate min (basetype = timestamp, sfunc1 = timestampsmaller, stype1 = timestamp, stype2 = timestamp); drop function timestamplarger (timestamp,timestamp); create function timestamplarger (timestamp,timestamp) returns timestamp as ' begin if $1 < $2 then return $2; else return $1; end if; end; ' language 'plpgsql'; drop aggregate max timestamp; create aggregate max (basetype = timestamp, sfunc1 = timestamplarger, stype1 = timestamp, stype2 = timestamp); drop function timesmaller (time,time); create function timesmaller (time,time) returns time as ' begin if $1 > $2 then return $2; else return $1; end if; end; ' language 'plpgsql'; drop aggregate min time; create aggregate min (basetype = time, sfunc1 = timesmaller, stype1 = time, stype2 = time); drop function timelarger (time,time); create function timelarger (time,time) returns time as ' begin if $1 < $2 then return $2; else return $1; end if; end; ' language 'plpgsql'; drop aggregate max time; create aggregate max (basetype = time, sfunc1 = timelarger, stype1 = time, stype2 = time); drop function oidsmaller (oid,oid); create function oidsmaller (oid, oid) returns oid as ' begin if $1 > $2 then return $2; else return $1; end if; end; ' language 'plpgsql'; drop function oidlarger (oid,oid); create function oidlarger (oid, oid) returns oid as ' begin if $1 < $2 then return $2; else return $1; end if; end; ' language 'plpgsql'; drop aggregate max oid; create aggregate max (basetype = oid, sfunc1 = oidlarger, stype1 = oid, stype2 = oid); drop aggregate min oid; create aggregate min (basetype = oid, sfunc1 = oidsmaller, stype1 = oid, stype2 = oid); drop function txtsmaller(text,text); create function txtsmaller(text, text) returns text as ' begin if $1 > $2 then return $2; else return $1; end if; end; ' language 'plpgsql'; drop aggregate min text; create aggregate min (basetype = text, sfunc1 = txtsmaller, stype1 = text, stype2 = text); drop function txtlarger (text,text); create function txtlarger (text, text) returns text as ' begin if $1 < $2 then return $2; else return $1; end if; end; ' language 'plpgsql'; drop aggregate max text; create aggregate max (basetype = text, sfunc1 = txtlarger, stype1 = text, stype2 = text); drop table b; create table b (a varchar(6),d date, t time,dt timestamp, i interval); insert into b values ('ZETA','12-05-1998','10:20','1997-11-21 10:23',interval '3:31'); insert into b values ('BETA','12-11-1998','22:50','1996-13-30 12:31',interval '2:10'); insert into b values ('ALFA','22-02-1996','12:36','1994-02-11 11:24',interval '1:30'); insert into b values ('GAMA','11-10-1968','16:30','1994-10-31 02:11',interval '2:33'); insert into b values ('NETA',current_date,current_time,current_timestamp,interval '2:33'); select min(a),min(oid),min(d),min(t),min(dt),min(i) from b; select max(a),max(oid),max(d),max(t),max(dt),max(i) from b; select oid,* from b; --------------DAC176195F4C064AA7CEA545--