Re: timestamps, formatting, and internals

On 2012-05-18, David Salisbury <salisbury(at)globe(dot)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