On 2012-05-18, David Salisbury <salisbury@globe.gov> wrote:
> So one question I have is if there a way to set PG in the way Oracle does it..
probably not.
> set nls_date_format = 'YYYY...' so I can query and see exactly what PG is seeing,
> even to the microseconds?
set datestyle to 'ISO';
> Is there a config parameter I can set in PG so that calculations
> are done only to the second?
no, but you can truncate explicitly
date_trunc('second',your_timestamp_expression)
The query: "show integer_datetimes;" should return 'on' which means
timestamps are microsecond precision if it returns 'off' your database
was built with floating point timstamps and equality tests will be
unreliable,
> 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.
you didn't show the actual join only the where clause.
given your task I would create CTEs finding the first record before
and after local solar noon, then combine them with a union and feed
the result of that to another disctint on to filter the most
appropriate record for each site.
--
⚂⚃ 100% natural