pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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;

view thread (3+ messages)  latest in thread

Message-ID: <37CE6DA6.B40F424E@sferacarta.com>
Permalink:  ../37CE6DA6.B40F424E@sferacarta.com/
Also on:    postgresql.org/message-id/37CE6DA6.B40F424E@sferacarta.com

 · 

reply

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