[Babase] Re: monthly means for weather data

Karl O. Pinc kop at meme.com
Thu Oct 14 12:11:04 EDT 2010


On 10/14/2010 10:43:17 AM, Niki Learn wrote:
> Karl,
> 
>  
> 
> Can you make it so I can use the rnkdate function with the daytime
> stamp in
> min_maxs? 

Done.  You can now use rnkdate on all timestamp values in the db.

> This will let me collect monthly rainfall and mean temp
> data to
> go with a bunch of cort samples.

In theory you are better off using stock postgres functions
because rnkdate just happens to return a date, as opposed
to the yymm it used to return.  In practice YMMV.

select date_trunc('month', wrdaytime) from min_maxs limit 5;

or

select date_trunc('month', wrdaytime)::date from min_maxs limit 5;

> 
>  
> 
> Thanks,
> 
> Niki
> 
>  
> 
> SQL error:
> 
> ERROR:  function rnkdate(timestamp without time zone) does not exist
> 
> HINT:  No function matches the given name and argument types. You may
> need
> to add explicit type casts.
> 
> In statement:
> 
> select prep.sname, extract(year from date) as year, hydroyear(date),
> season(date), date as sampledate, gc, rnkdate(date), rank, grp as
> rankgrp,
> (date - birth)/365.25 as sampleage, sum(rain), avg(tempmin),
> avg(tempmax)
> from prep, results, ranks, biograph, maturedates, min_maxs where
> prep.sid =
> results.sid and prep.sname = ranks.sname and ranks.sname =
> biograph.sname
> and prep.sname = maturedates.sname and rnkdate(date) = rnkdate and gc
> <> 0
> and rnktype = 'ALM' and date >= '2002-01-01' and date >= matured and
> rnkdate(date) = rnkdate(wrdaytime) order by date;
> 
>  
> 
> 




Karl <kop at meme.com>
Free Software:  "You don't pay back, you pay forward."
                 -- Robert A. Heinlein




More information about the Babase mailing list