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
Here's the immediate code I'm thinking is in question.
SELECT DISTINCT on ( site_id, solarnoon )
yada.
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
