On Mon, 4 Mar 2024 at 14:06, <albrecht.dress@posteo.de> 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.