On 05/18/2012 04: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.
I am not sure I follow. Timestamps(which is what I think you are referring to) can be stored with up to microsecond precision and the values will be returned at the precision specified.
See here; http://www.postgresql.org/docs/9.0/interactive/datatype-datetime.html
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?
Maybe you are looking for data formatting?: http://www.postgresql.org/docs/9.0/interactive/functions-formatting.html 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
-- Adrian Klaver adrian.klaver@xxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
