Re: timestamps, formatting, and internals

David Salisbury <salisbury(at)globe(dot)gov> writes:
> Actually, figured I'd post the whole function, painful as it
> might be for anyone to read. If anyone sees something that's a bit
> of a risk ( like perhaps the whole thing ;)

Well, I don't know exactly what's causing your issue, but I see a few
things that seem rather dubious:

> min(
> abs(
> cast(
> extract(
> epoch FROM (
> measured_at -
> calculate_local_solar_noon(measured_at,longitude)
> )
> ) as integer
> )
> )
> ) as
> minimum_time_between_measured_and_solarnoon,

Is there a really good reason to force the interval value to integer
here? I forget offhand whether you get truncation or rounding when you
do that, but in either case it's entirely likely that the computed min()
will be less than the actual difference for *any* specific real site, if
the smallest such difference has a fractional part. I'd lose the CAST
step and see what happens.

> (
> (
> 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
> )
> )
> )

Because of the CAST above, these tests are guaranteed to fail if the
measured_at value has a fractional-second part, and I'm not sure why you
are assuming that that should be zero. Also, the above is an expensive,
grotty, imprecise way to convert a number back to an interval. Consider
using

sds.minimum_time_between_measured_and_solarnoon * interval '1 second'

or even better, what about

abs (extract (epoch from (
sd.measured_at -
calculate_local_solar_noon(sd.measured_at,sds.longitude)))) <=
sds.minimum_time_between_measured_and_solarnoon

which seems to me to be a lot more naturally related to what you're
doing to compute minimum_time_between_measured_and_solarnoon in the
first place.

regards, tom lane