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.
