Re: [GENERAL] aggregate question

Hello Ulf,

mercoledì, 21 ottobre 98, you wrote:

UM> I posted the questions below a few days ago to the SQL list, but
UM> apparently there is nobody there who can help -- maybe I'm luckier in
UM> this list?  :-)

UM> ----------------------------------------------------------------------
UM> I'm a novice to postgresql, so please excuse me if I'm addressing to
UM> the wrong mailing list!

UM> I need to specify some column functions for statistical analysis,
UM> e.g. standard deviation or variance. After reading the user's guide I
UM> think that's to realize with aggregates, but with those docs only I
UM> cannot figure out how to do it -- does anybody know whether there is a
UM> ready-for-use "statistics" package, or how to write something that's
UM> usable in a SQL statement like this:

UM>           select date, avg(temperature), stddev(temperature)
UM>           from temperatures              ^^^^^^
UM>           where date between '01.01.1999' and '15.02.1999'
UM>           group by date                                      ?
This query is OK, except for STDDEV function, you have to create this
function, I think we don't have it on PostgreSQL.

UM> Other question: I'm used to use DB2/2, and DB2/2 has functions like
UM> "month(some_date)" and "hour(some_timestamp)". Are there equivalents
UM> in postgresql?

There's the SQL92 EXTRACT function...
look, this is the actual date and time:

prova=> select current_timestamp as today;
today
----------------------
1998-10-21 17:03:16+02
(1 row)

How to display the month:
prova=> select extract(month from current_date) as month;
month
-----
   10
(1 row)

Unfortunately, for now, the EXTRACT function works only with DATE, DATETIME
and TIMESPAN data types. You have to CAST a TIMESTAMP or a TIME as DATETIME to
extract a field from it as in:

prova=> select extract(hour from cast(current_timestamp as datetime))
        as hour;
hour
----
  16
(1 row)

Jose'