pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMax function on Timestamp
3+ messages / 3 participants
[nested] [flat]
* Max function on Timestamp
@ 1999-09-02 08:30 Esteban Chiner Sanz <echiner@tissat.es>
0 siblings, 2 replies; 3+ messages in thread
From: Esteban Chiner Sanz @ 1999-09-02 08:30 UTC (permalink / raw)
To: pgsql-general@postgresql.org; pgsql-sql
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)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [GENERAL] Max function on Timestamp
@ 1999-09-02 12:29 José Soares <jose@sferacarta.com>
parent: Esteban Chiner Sanz <echiner@tissat.es>
1 sibling, 0 replies; 3+ messages in thread
From: José Soares @ 1999-09-02 12:29 UTC (permalink / raw)
To: Esteban Chiner Sanz <echiner@tissat.es>; +Cc: pgsql-general@postgresql.org; pgsql-sql
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;
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Max function on Timestamp
@ 1999-09-06 11:37 Herouth Maoz <herouth@oumail.openu.ac.il>
parent: Esteban Chiner Sanz <echiner@tissat.es>
1 sibling, 0 replies; 3+ messages in thread
From: Herouth Maoz @ 1999-09-06 11:37 UTC (permalink / raw)
To: Esteban Chiner Sanz <echiner@tissat.es>; pgsql-sql
At 11:30 +0300 on 02/09/1999, Esteban Chiner Sanz wrote:
> 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?
You can convert to datetime and back. I have 6.4, in which conversion from
datetime to timestamp is broken. I don't know whether or not it is broken
in 6.5.1 as well, but if it is, you can easily define:
CREATE FUNCTION dt_to_ts( datetime ) returns timestamp
AS 'SELECT timestamp_in( datetime_out( $1 ) ) WHERE $1 IS NOT NULL'
LANGUAGE 'sql';
Now, get your max with:
SELECT dt_to_ts( max( timestamp_field::datetime ) )
FROM the_table;
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~1999-09-06 11:37 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-09-02 08:30 Max function on Timestamp Esteban Chiner Sanz <echiner@tissat.es>
1999-09-02 12:29 ` José Soares <jose@sferacarta.com>
1999-09-06 11:37 ` Herouth Maoz <herouth@oumail.openu.ac.il>
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