timestamp - timestamp result

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

timestamp - timestamp result

Thomas Kellerer-4
I regularly see people suggesting to use

    extract(day from one_timestamp - other_timestamp)

to calculate the difference between two timestamps in days.

But I wonder if the "format" of the resulting interval is guaranteed to only have days
(and not months or years)

The following:

     timestamp '2020-06-26 17:00:00' - timestamp '2019-04-01 14:00:00'

returns an interval like this:

     0 years 0 mons 452 days 3 hours 0 mins 0.0 secs

However, is there ever a chance that the expression will yield the (equivalent) interval:

     1 years 2 mons 25 days 3 hours 0 mins 0.0 secs

e.g. as the age() function does.

Is it safe to assume that "timestamp - timestamp" will never contain units larger then days?


Thomas



Reply | Threaded
Open this post in threaded view
|

Re: timestamp - timestamp result

Pavel Stehule


pá 26. 6. 2020 v 7:29 odesílatel Thomas Kellerer <[hidden email]> napsal:
I regularly see people suggesting to use

    extract(day from one_timestamp - other_timestamp)

to calculate the difference between two timestamps in days.

But I wonder if the "format" of the resulting interval is guaranteed to only have days
(and not months or years)

The following:

     timestamp '2020-06-26 17:00:00' - timestamp '2019-04-01 14:00:00'

returns an interval like this:

     0 years 0 mons 452 days 3 hours 0 mins 0.0 secs

However, is there ever a chance that the expression will yield the (equivalent) interval:

     1 years 2 mons 25 days 3 hours 0 mins 0.0 secs

postgres=# select age(timestamp '2020-06-26 17:00:00',timestamp '2019-04-01 14:00:00');
┌────────────────────────────────┐
│              age               │
╞════════════════════════════════╡
│ 1 year 2 mons 25 days 03:00:00 │
└────────────────────────────────┘
(1 row)
 

e.g. as the age() function does.

Is it safe to assume that "timestamp - timestamp" will never contain units larger then days?

Now, this operator internally calls only interval_justify_hours functions. So if somebody doesn't change related code, you can expect so only days, hours field's are changed.

Regards

Pavel


 


Thomas



Reply | Threaded
Open this post in threaded view
|

Re: timestamp - timestamp result

Tom Lane-2
Pavel Stehule <[hidden email]> writes:
> pá 26. 6. 2020 v 7:29 odesílatel Thomas Kellerer <[hidden email]> napsal:
>> Is it safe to assume that "timestamp - timestamp" will never contain units
>> larger then days?

> Now, this operator internally calls only interval_justify_hours functions.

We should probably document that --- I'll go do so.

                        regards, tom lane