David Salisbury <salisbury@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
