Actually, figured I'd post the whole function, painful as it
might be for anyone to read. If anyone sees something that's a bit
of a risk ( like perhaps the whole thing ;)
On 5/18/12 5:19 PM, David Salisbury wrote:
I'm trying to debug an intermittent problem I'm seeing in one of our
rollup scripts.
I'll try to summarize. A table has a measured_at field, of which I
calculate another
time value based on that field and a longitude value, called solar_noon,
and I summarize
min/max values grouped around this solarnoon. While I'm doing this I
also calculate a
minimum time difference between the calcualted solar noon value and all
the measured_at times
within the group. I then join this summary table back with the original
table it's
summarizing, trying to pick out the one record in it that has the
measured_at value that's closest
to the solarnoon value of the grouping.
Clear as mud? Anyways, there seems to be a problem on that last part.
I'm thinking
the join on these date values is a bit funky. Perhaps things aren't
matching up on micro
second values, but it's hard to know with queries if I'm seeing what the
DB is seeing, as
date values are stored in seconds and what queries give you is a format
of that.
So one question I have is if there a way to set PG in the way Oracle
does it..
set nls_date_format = 'YYYY...' so I can query and see exactly what PG
is seeing,
even to the microseconds? Is there a config parameter I can set in PG so
that calculations
are done only to the second? It seems this join doesn't always find a
record that's closest
to solar noon, and therefore drops the summary and join record all
together.
PG 9.0, Linux
execute "
CREATE OR REPLACE FUNCTION rollup_and_insert_subdays()
returns void as $$
declare v_created_at timestamp without time zone;
BEGIN
v_created_at := now();
INSERT INTO air_temp_dailies (
uuid,
site_id,
organizationid,
protocol_id,
measured_at,
current_temp_c,
maximum_temp_c,
minimum_temp_c,
created_at,
comments
)
SELECT
subd_summary.uuid,
subd_summary.site_id,
subd_summary.organizationid,
subd_summary.protocol_id,
subd_summary.measured_at,
subd_summary.current_temp_c,
subd_summary.max_temp_c,
subd_summary.min_temp_c,
v_created_at as updated_at,
'automated station rollup' as comments
FROM (
SELECT DISTINCT on ( site_id, solarnoon )
site_id,
organizationid,
protocol_id,
uuid,
solarnoon,
measured_at,
current_temp_c,
max_temp_c,
min_temp_c
FROM (
SELECT
sd.site_id,
sd.organizationid,
sd.protocol_id,
sd.uuid,
sds.solarnoon,
sd.measured_at,
sd.current_temp_c,
sds.max_temp_c as max_temp_c,
sds.min_temp_c as min_temp_c
FROM (
SELECT
site_id,
longitude,
calculate_local_solar_noon(measured_at,longitude) as solarnoon,
max(current_temp_c) as max_temp_c,
min(current_temp_c) as min_temp_c,
min(
abs(
cast(
extract(
epoch FROM (
measured_at -
calculate_local_solar_noon(measured_at,longitude)
)
) as integer
)
)
) as
minimum_time_between_measured_and_solarnoon,
trunc(count(*)/93) as enough_measurements
FROM (
SELECT
site_id,
current_temp_c,
measured_at,
ST_X(point) as longitude
FROM
air_temp_sub_days INNER JOIN sites on (
air_temp_sub_days.site_id = sites.id
)
) as appending_longitude_to_sub_day_values
WHERE
measured_at is not null
GROUP BY
site_id,
calculate_local_solar_noon(measured_at,longitude),
-- we assume that for any site, for any given solarnoon, the
-- longitude is constant, so adding this value in the
-- group by has no effect other than allowing these values
-- to percolate to the outer select
longitude
) sds,
air_temp_sub_days sd
WHERE
sds.site_id = sd.site_id
and
calculate_local_solar_noon(
sd.measured_at,
sds.longitude
) = sds.solarnoon
-- match with the record that's closest to solarnoon.
-- At this point we know the time difference,
-- but not whether it's more or less. The higher level
-- DISTINCT clause removes any duplicates caused should
-- solarnoon fall exactly between two measured_at times.
and
enough_measurements > 0
and
(
(
sd.measured_at = (
calculate_local_solar_noon(sd.measured_at,sds.longitude) + (
sds.minimum_time_between_measured_and_solarnoon::text ||
' secs'
)::interval
)
)
or
(
sd.measured_at = (
calculate_local_solar_noon(sd.measured_at,sds.longitude) - (
sds.minimum_time_between_measured_and_solarnoon::text ||
' secs'
)::interval
)
)
)
) end_distinct
) subd_summary
LEFT OUTER JOIN air_temp_dailies on
subd_summary.site_id = air_temp_dailies.site_id
and
subd_summary.measured_at = air_temp_dailies.measured_at
WHERE
air_temp_dailies.site_id is null
and
air_temp_dailies.measured_at is null
order by subd_summary.site_id, subd_summary.measured_at
;
END $$ LANGUAGE plpgsql;
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
