Have you tested DATE_TRUNC()?
Not really; it returns a full timestamp and I would still have to concatenate strings. My goal is to speed up the following code (where p_ parameters are user inputs):
date(extract(YEAR FROM m.taken)||''-'||p_month1||'-'||p_day1||''') d1,
date(extract(YEAR FROM m.taken)||''-'||p_month2||'-'||p_day2||''') d2
Using DATE_TRUNC() won't help here, as far as I can tell. Removing the concatenation will halve the query's time. Such as:
dateserial( m.taken, p_month1, p_day1 ) d1,
dateserial( m.taken, p_month2, p_day2 ) d2
dateserial( m.taken, p_month2, p_day2 ) d2
My testing so far has shown a modest improvement by using a C function (to avoid concatenation).
Dave
