Re: Inconsistent time interval formatting

Re: Inconsistent time interval formatting

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'.

Re: Inconsistent time interval formatting

Allen Chen  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

Re: Inconsistent time interval formatting

Gary Chambers  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

Re: Inconsistent time interval formatting

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 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.

I have the same issue on 8.4. Fixing it is low on my task list, but I too would like to know why.

Re: Inconsistent time interval formatting

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.

Re: Inconsistent time interval formatting

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 days 03: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.

Re: Inconsistent time interval formatting

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

Re: Inconsistent time interval formatting

Inconsistent time interval formatting

Re: Inconsistent time interval formatting

>> 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

Re: Inconsistent time interval formatting

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 days 03: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 forgot got wrap with justify_hours.