Thread: Max function on Timestamp

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)

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)

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

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