On Mon, 4 Mar 2024 at 14:06, <albrecht.dress@xxxxxxxxx> wrote: > Am 04.03.2024 13:45 schrieb Francisco Olarte: > > Intervals are composed of months, days and seconds, as not every month > > has 30 days and not every day has 86400 seconds, so to compare them > > you have to normalize them somehow, which can lead to bizarre results. > Ah, I see, thanks for the explanation. I had the (apparently wrong) > impression that Postgres _internally_ always uses numerical values (i.e. > the equivalent of EXTRACT(EPOCH …)) for such calculations. My bad… > However, a clarification in the docs might be helpful! Ah, the elusive timestamp/interval clarification. Intervals being three numbers, and timestamp(tz) being just a fancy one with fancy text conversions and arithmetic rules take a lot of this list bandwidth. I work with telephony and similar things with use a lot of durations, which I just store in numeric/real/integer columns, but due to history and some reporting convenience I store points in time as timestamps. Adding is easy, just do ts_col + duration_col * '1 second'::interval, but I'm really looking forward to convert ts[tz] to numbers too, so I can substract them. Substracting is a bit more hairy, but extract epoch on the result normally solves it, as they are justified intervals. But I just made some memory refreshing queries and they still manage to surprise me, and I've been using postgres from before sql. Francisco Olarte.
