From: José Soares <jose@sferacarta.com>
To: Esteban Chiner Sanz <echiner@tissat.es>
Cc: pgsql-general@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: [GENERAL] Max function on Timestamp
Date: Thu, 02 Sep 1999 14:29:26 +0200
Message-ID: <37CE6DA6.B40F424E@sferacarta.com> (raw)
References: <37CE3592.2558E205@tissat.es>
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)
>
> ************
Attachments:min_max.sql (text/plain, 3.5K) download
-- 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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: jose@sferacarta.com, echiner@tissat.es, pgsql-general@postgresql.org
Subject: Re: [GENERAL] Max function on Timestamp
In-Reply-To: <37CE6DA6.B40F424E@sferacarta.com>
References: <37CE3592.2558E205@tissat.es> <37CE6DA6.B40F424E@sferacarta.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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