timestamps, formatting, and internals

[David Salisbury <salisbury@xxxxxxxxx>]

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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general