Thread: Inconsistent time interval formatting
Has anyone else out there noticed inconsistencies in how pgsql formats time intervals over 1 day?
For example, I have a query that returns a column of intervals and I get output like this:
30:30:00
1 day 03:02:47
1 day 01:38:34
26:25:29.50
Why do some of the intervals show days broken out whereas others only show hours? I have seen intervals left in hours even when the intervals are more than two days long. FWIW, I would prefer if it was always left in hours, but would be happy if it would just be consistent either way.
I'm running PostgreSQL 8.3.12, but I have seen this in earlier versions I've used too.
-Allen
For example, I have a query that returns a column of intervals and I get output like this:
30:30:00
1 day 03:02:47
1 day 01:38:34
26:25:29.50
Why do some of the intervals show days broken out whereas others only show hours? I have seen intervals left in hours even when the intervals are more than two days long. FWIW, I would prefer if it was always left in hours, but would be happy if it would just be consistent either way.
I'm running PostgreSQL 8.3.12, but I have seen this in earlier versions I've used too.
-Allen
On Jan 13, 2011, at 9:34 AM, Allen Chen wrote: > Has anyone else out there noticed inconsistencies in how pgsql formats time intervals over 1 day? > > For example, I have a query that returns a column of intervals and I get output like this: > > 30:30:00 > 1 day 03:02:47 > 1 day 01:38:34 > 26:25:29.50 > > Why do some of the intervals show days broken out whereas others only show hours? I have seen intervals left in hourseven when the intervals are more than two days long. FWIW, I would prefer if it was always left in hours, but wouldbe happy if it would just be consistent either way. > > I'm running PostgreSQL 8.3.12, but I have seen this in earlier versions I've used too. I have the same issue on 8.4. Fixing it is low on my task list, but I too would like to know why.
>> Why do some of the intervals show days broken out whereas others only >> show hours? I have seen intervals left in hours even when the intervals >> are more than two days long. FWIW, I would prefer if it was always left >> in hours, but would be happy if it would just be consistent either way. > I have the same issue on 8.4. Fixing it is low on my task list, but I too > would like to know why. An interim fix might be to cast your output to interval(0) (or to whatever degree of precision you might need). I believe I encountered this behavior some time ago and casting solved the problem for me. -- Gary Chambers
On 01/13/11 9:34 AM, Allen Chen wrote:
> Has anyone else out there noticed inconsistencies in how pgsql formats
> time intervals over 1 day?
>
> For example, I have a query that returns a column of intervals and I
> get output like this:
>
> 30:30:00
> 1 day 03:02:47
> 1 day 01:38:34
> 26:25:29.50
>
> Why do some of the intervals show days broken out whereas others only
> show hours? I have seen intervals left in hours even when the
> intervals are more than two days long. FWIW, I would prefer if it was
> always left in hours, but would be happy if it would just be
> consistent either way.
Interval is a complex data type, with months, days, and seconds as
components. not all days have 24 hours due to DST changes.
ok, here's the paragraph from the manual, explains it better than I can...
Internallyintervalvalues are stored as months, days, and seconds.
This is done because the number of days in a month varies, and a day
can have 23 or 25 hours if a daylight savings time adjustment is
involved. The months and days fields are integers while the seconds
field can store fractions. Because intervals are usually created
from constant strings ortimestampsubtraction, this storage method
works well in most cases.
Functions|justify_days|and|justify_hours|are available for adjusting
days and hours that overflow their normal ranges.
Gary Chambers <gwchamb@gwcmail.com> writes:
>>> Why do some of the intervals show days broken out whereas others only
>>> show hours? I have seen intervals left in hours even when the intervals
>>> are more than two days long. FWIW, I would prefer if it was always left
>>> in hours, but would be happy if it would just be consistent either way.
>> I have the same issue on 8.4. Fixing it is low on my task list, but I too
>> would like to know why.
> An interim fix might be to cast your output to interval(0) (or to whatever
> degree of precision you might need). I believe I encountered this behavior
> some time ago and casting solved the problem for me.
That won't really help. The fundamental point here is that '1 day' is
not the same concept as '24 hours', because of DST changes; and the
interval type treats them as different.
If you don't care about that, you can use justify_hours (I think that's
the right function) to smash them to the same thing.
But I suspect the OP's real complaint would be better solved by use of
to_char() to produce an output format that includes zeroes instead of
dropping fields that are zero.
regards, tom lane
On Jan 13, 2011, at 11:03 AM, Tom Lane wrote: > If you don't care about that, you can use justify_hours (I think that's > the right function) to smash them to the same thing. I use justify_hours, and I still get entries like '1 day 35:31:10' intermixed with the entires I'd expect like '2 days 03:12:40'.
On 01/13/11 1:08 PM, Ben Chobot wrote: > On Jan 13, 2011, at 11:03 AM, Tom Lane wrote: > >> If you don't care about that, you can use justify_hours (I think that's >> the right function) to smash them to the same thing. > I use justify_hours, and I still get entries like '1 day 35:31:10' intermixed with the entires I'd expect like '2 days03:12:40'. test=> select justify_hours(interval '1 day 35:31:10'); justify_hours ----------------- 2 days 11:31:10 (1 row) works here. I believe this is an 8.3.9 system (overdue for upgrades) that I happened to test on.
That won't really help. The fundamental point here is that '1 day' is
not the same concept as '24 hours', because of DST changes; and the
interval type treats them as different.
If you don't care about that, you can use justify_hours (I think that's
the right function) to smash them to the same thing.
But I suspect the OP's real complaint would be better solved by use of
to_char() to produce an output format that includes zeroes instead of
dropping fields that are zero.
regards, tom lane
Hi Tom,
I don't understand how DST changes matter for a time interval or how that could even be factored into calculations. Could you elaborate on that? I had a query today that returned an interval of 70:23:06.935933. Wouldn't that be at least two days regardless of DST?
Thanks for shining the light on justify_hours, though. I did not know that function existed. That does give me a way to have consistent output for reporting.
Thanks to everyone who replied!
-Allen
On 01/13/2011 12:55 PM, Allen Chen wrote: > > > That won't really help. The fundamental point here is that '1 day' is > not the same concept as '24 hours', because of DST changes; and the > interval type treats them as different. > > If you don't care about that, you can use justify_hours (I think that's > the right function) to smash them to the same thing. > > But I suspect the OP's real complaint would be better solved by use of > to_char() to produce an output format that includes zeroes instead of > dropping fields that are zero. > > regards, tom lane > > > Hi Tom, > > I don't understand how DST changes matter for a time interval or how > that could even be factored into calculations. Could you elaborate on > that? I had a query today that returned an interval of > 70:23:06.935933. Wouldn't that be at least two days regardless of DST? > > Thanks for shining the light on justify_hours, though. I did not know > that function existed. That does give me a way to have consistent > output for reporting. > > Thanks to everyone who replied! > > -Allen > I think to help with this we will need the complete cycle, in other words the queries you are using to generate the intervals as well as the resultant intervals. -- Adrian Klaver adrian.klaver@gmail.com
On Jan 13, 2011, at 1:15 PM, John R Pierce wrote: > On 01/13/11 1:08 PM, Ben Chobot wrote: >> On Jan 13, 2011, at 11:03 AM, Tom Lane wrote: >> >>> If you don't care about that, you can use justify_hours (I think that's >>> the right function) to smash them to the same thing. >> I use justify_hours, and I still get entries like '1 day 35:31:10' intermixed with the entires I'd expect like '2 days03:12:40'. Ho ho... for the record, it turns out that the one column in our report which has these issues is the one somebody forgotgot wrap with justify_hours.
Allen Chen <rocklob@gmail.com> writes:
>> That won't really help. The fundamental point here is that '1 day' is
>> not the same concept as '24 hours', because of DST changes; and the
>> interval type treats them as different.
> I don't understand how DST changes matter for a time interval or how that
> could even be factored into calculations. Could you elaborate on that?
The main case where it matters is timestamp plus or minus interval.
As an example, 2011-03-13 is a DST transition day where I live. So:
regression=# select '2011-03-13 01:00'::timestamptz;
timestamptz
------------------------
2011-03-13 01:00:00-05
(1 row)
regression=# select '2011-03-13 01:00'::timestamptz + '1 day'::interval;
?column?
------------------------
2011-03-14 01:00:00-04
(1 row)
regression=# select '2011-03-13 01:00'::timestamptz + '24 hours'::interval;
?column?
------------------------
2011-03-14 02:00:00-04
(1 row)
"Add 1 day" means "produce the same local time on the next day", whereas
"add 24 hours" means exactly that.
regards, tom lane