format interval as days

#1

Hi!

I'd like to represent an interval like '1 day 1h 2m 12s' in days, in
this case 25

Apparently

SELECT to_char(interval '1 day 1h 2m 12s', ' HH24');

returns only one hour.

I read that HH24 can return more than 24 hours, which it does with e.g.:
SELECT to_char(interval '25h 2m 12s', ' HH24');

Is there a more convenient way of returning the number of hours than
with a contruct like the following?
SELECT EXTRACT( DAY FROM interval '1 day 25h 2m 12s')*24 + EXTRACT(
HOUR FROM interval '1 day 25h 2m 12s');

And what if the interval is longer than a month?
How many day's has an "interval-month"?

It seems that I'm missing something..

Thanks
Patrick

#2

Patrick Scharrenberg <pittipatti (AT) web (DOT) de> wrote:

Quote:

I'd like to represent an interval like '1 day 1h 2m 12s' in days, in
this case 25

I assume you mean "hours".

Quote:

Apparently

SELECT to_char(interval '1 day 1h 2m 12s', ' HH24');

returns only one hour.

I read that HH24 can return more than 24 hours, which it does with e.g.:
SELECT to_char(interval '25h 2m 12s', ' HH24');

Internally, an interval is stored as a time value plus a number of days
and months (see include/utils/timestamp.h)
Months are automatically converted to years and months, but days are
never converted to months or hours and vice versa:

test=> SELECT '26 months 3 days'::interval;
interval
-----------------------
2 years 2 mons 3 days
(1 row)

test=> SELECT '25 months 33 days'::interval;
interval
-----------------------
2 years 1 mon 33 days
(1 row)

test=> SELECT '25 months 32 days 24 hours'::interval;
interval
--------------------------------
2 years 1 mon 32 days 24:00:00
(1 row)

Quote:

Is there a more convenient way of returning the number of hours than
with a contruct like the following?
SELECT EXTRACT( DAY FROM interval '1 day 25h 2m 12s')*24 + EXTRACT(
HOUR FROM interval '1 day 25h 2m 12s');

I don't think there is a better way. You could write an SQL function around
it:

CREATE FUNCTION int_hours(interval) RETURNS integer
LANGUAGE sql IMMUTABLE STRICT AS
'SELECT
CAST (
(
(
(
EXTRACT(YEARS FROM $1) * 12 + EXTRACT(MONTHS FROM $1)
) * 30 + EXTRACT(DAY FROM $1)
) * 24 + EXTRACT(HOURS FROM $1)
)
AS integer)';

test=> SELECT int_hours('25 months 32 days 24 hours');
int_hours
-----------
18792
(1 row)

Quote:

And what if the interval is longer than a month?
How many day's has an "interval-month"?

30.

Without knowing which month it is, you cannot tell its accurate length.
That is an inherent problem of an interval data type.

There is an instructive comment in include/utils/timestamp.h:
/*
* DAYS_PER_MONTH is very imprecise. The more accurate value is
* 365.2425/12 = 30.436875, or '30 days 10:29:06'. Right now we only
* return an integral number of days, but someday perhaps we should
* also return a 'time' value to be used as well. ISO 8601 suggests
* 30 days.
*/
#define DAYS_PER_MONTH 30 /* assumes exactly 30 days per month */

Yours,
Laurenz Albe

#3

Patrick Scharrenberg <pittipatti (AT) web (DOT) de> wrote:

Quote:

I'd like to represent an interval like '1 day 1h 2m 12s' in days, in
this case 25

I assume you mean "hours".

Quote:

Apparently

SELECT to_char(interval '1 day 1h 2m 12s', ' HH24');

returns only one hour.

I read that HH24 can return more than 24 hours, which it does with e.g.:
SELECT to_char(interval '25h 2m 12s', ' HH24');

Internally, an interval is stored as a time value plus a number of days
and months (see include/utils/timestamp.h)
Months are automatically converted to years and months, but days are
never converted to months or hours and vice versa:

test=> SELECT '26 months 3 days'::interval;
interval
-----------------------
2 years 2 mons 3 days
(1 row)

test=> SELECT '25 months 33 days'::interval;
interval
-----------------------
2 years 1 mon 33 days
(1 row)

test=> SELECT '25 months 32 days 24 hours'::interval;
interval
--------------------------------
2 years 1 mon 32 days 24:00:00
(1 row)

Quote:

Is there a more convenient way of returning the number of hours than
with a contruct like the following?
SELECT EXTRACT( DAY FROM interval '1 day 25h 2m 12s')*24 + EXTRACT(
HOUR FROM interval '1 day 25h 2m 12s');

I don't think there is a better way. You could write an SQL function around
it:

CREATE FUNCTION int_hours(interval) RETURNS integer
LANGUAGE sql IMMUTABLE STRICT AS
'SELECT
CAST (
(
(
(
EXTRACT(YEARS FROM $1) * 12 + EXTRACT(MONTHS FROM $1)
) * 30 + EXTRACT(DAY FROM $1)
) * 24 + EXTRACT(HOURS FROM $1)
)
AS integer)';

test=> SELECT int_hours('25 months 32 days 24 hours');
int_hours
-----------
18792
(1 row)

Quote:

And what if the interval is longer than a month?
How many day's has an "interval-month"?

30.

Without knowing which month it is, you cannot tell its accurate length.
That is an inherent problem of an interval data type.

There is an instructive comment in include/utils/timestamp.h:
/*
* DAYS_PER_MONTH is very imprecise. The more accurate value is
* 365.2425/12 = 30.436875, or '30 days 10:29:06'. Right now we only
* return an integral number of days, but someday perhaps we should
* also return a 'time' value to be used as well. ISO 8601 suggests
* 30 days.
*/
#define DAYS_PER_MONTH 30 /* assumes exactly 30 days per month */

Yours,
Laurenz Albe

#4

Patrick Scharrenberg <pittipatti (AT) web (DOT) de> wrote:

Quote:

I'd like to represent an interval like '1 day 1h 2m 12s' in days, in
this case 25

I assume you mean "hours".

Quote:

Apparently

SELECT to_char(interval '1 day 1h 2m 12s', ' HH24');

returns only one hour.

I read that HH24 can return more than 24 hours, which it does with e.g.:
SELECT to_char(interval '25h 2m 12s', ' HH24');

Internally, an interval is stored as a time value plus a number of days
and months (see include/utils/timestamp.h)
Months are automatically converted to years and months, but days are
never converted to months or hours and vice versa:

test=> SELECT '26 months 3 days'::interval;
interval
-----------------------
2 years 2 mons 3 days
(1 row)

test=> SELECT '25 months 33 days'::interval;
interval
-----------------------
2 years 1 mon 33 days
(1 row)

test=> SELECT '25 months 32 days 24 hours'::interval;
interval
--------------------------------
2 years 1 mon 32 days 24:00:00
(1 row)

Quote:

Is there a more convenient way of returning the number of hours than
with a contruct like the following?
SELECT EXTRACT( DAY FROM interval '1 day 25h 2m 12s')*24 + EXTRACT(
HOUR FROM interval '1 day 25h 2m 12s');

I don't think there is a better way. You could write an SQL function around
it:

CREATE FUNCTION int_hours(interval) RETURNS integer
LANGUAGE sql IMMUTABLE STRICT AS
'SELECT
CAST (
(
(
(
EXTRACT(YEARS FROM $1) * 12 + EXTRACT(MONTHS FROM $1)
) * 30 + EXTRACT(DAY FROM $1)
) * 24 + EXTRACT(HOURS FROM $1)
)
AS integer)';

test=> SELECT int_hours('25 months 32 days 24 hours');
int_hours
-----------
18792
(1 row)

Quote:

And what if the interval is longer than a month?
How many day's has an "interval-month"?

30.

Without knowing which month it is, you cannot tell its accurate length.
That is an inherent problem of an interval data type.

There is an instructive comment in include/utils/timestamp.h:
/*
* DAYS_PER_MONTH is very imprecise. The more accurate value is
* 365.2425/12 = 30.436875, or '30 days 10:29:06'. Right now we only
* return an integral number of days, but someday perhaps we should
* also return a 'time' value to be used as well. ISO 8601 suggests
* 30 days.
*/
#define DAYS_PER_MONTH 30 /* assumes exactly 30 days per month */

Yours,
Laurenz Albe

#5

Patrick Scharrenberg <pittipatti (AT) web (DOT) de> wrote:

Quote:

I'd like to represent an interval like '1 day 1h 2m 12s' in days, in
this case 25

I assume you mean "hours".

Quote:

Apparently

SELECT to_char(interval '1 day 1h 2m 12s', ' HH24');

returns only one hour.

I read that HH24 can return more than 24 hours, which it does with e.g.:
SELECT to_char(interval '25h 2m 12s', ' HH24');

Internally, an interval is stored as a time value plus a number of days
and months (see include/utils/timestamp.h)
Months are automatically converted to years and months, but days are
never converted to months or hours and vice versa:

test=> SELECT '26 months 3 days'::interval;
interval
-----------------------
2 years 2 mons 3 days
(1 row)

test=> SELECT '25 months 33 days'::interval;
interval
-----------------------
2 years 1 mon 33 days
(1 row)

test=> SELECT '25 months 32 days 24 hours'::interval;
interval
--------------------------------
2 years 1 mon 32 days 24:00:00
(1 row)

Quote:

Is there a more convenient way of returning the number of hours than
with a contruct like the following?
SELECT EXTRACT( DAY FROM interval '1 day 25h 2m 12s')*24 + EXTRACT(
HOUR FROM interval '1 day 25h 2m 12s');

I don't think there is a better way. You could write an SQL function around
it:

CREATE FUNCTION int_hours(interval) RETURNS integer
LANGUAGE sql IMMUTABLE STRICT AS
'SELECT
CAST (
(
(
(
EXTRACT(YEARS FROM $1) * 12 + EXTRACT(MONTHS FROM $1)
) * 30 + EXTRACT(DAY FROM $1)
) * 24 + EXTRACT(HOURS FROM $1)
)
AS integer)';

test=> SELECT int_hours('25 months 32 days 24 hours');
int_hours
-----------
18792
(1 row)

Quote:

And what if the interval is longer than a month?
How many day's has an "interval-month"?

30.

Without knowing which month it is, you cannot tell its accurate length.
That is an inherent problem of an interval data type.

There is an instructive comment in include/utils/timestamp.h:
/*
* DAYS_PER_MONTH is very imprecise. The more accurate value is
* 365.2425/12 = 30.436875, or '30 days 10:29:06'. Right now we only
* return an integral number of days, but someday perhaps we should
* also return a 'time' value to be used as well. ISO 8601 suggests
* 30 days.
*/
#define DAYS_PER_MONTH 30 /* assumes exactly 30 days per month */

Yours,
Laurenz Albe

#6

Patrick Scharrenberg <pittipatti (AT) web (DOT) de> wrote:

Quote:

I'd like to represent an interval like '1 day 1h 2m 12s' in days, in
this case 25

I assume you mean "hours".

Quote:

Apparently

SELECT to_char(interval '1 day 1h 2m 12s', ' HH24');

returns only one hour.

I read that HH24 can return more than 24 hours, which it does with e.g.:
SELECT to_char(interval '25h 2m 12s', ' HH24');

Internally, an interval is stored as a time value plus a number of days
and months (see include/utils/timestamp.h)
Months are automatically converted to years and months, but days are
never converted to months or hours and vice versa:

test=> SELECT '26 months 3 days'::interval;
interval
-----------------------
2 years 2 mons 3 days
(1 row)

test=> SELECT '25 months 33 days'::interval;
interval
-----------------------
2 years 1 mon 33 days
(1 row)

test=> SELECT '25 months 32 days 24 hours'::interval;
interval
--------------------------------
2 years 1 mon 32 days 24:00:00
(1 row)

Quote:

Is there a more convenient way of returning the number of hours than
with a contruct like the following?
SELECT EXTRACT( DAY FROM interval '1 day 25h 2m 12s')*24 + EXTRACT(
HOUR FROM interval '1 day 25h 2m 12s');

I don't think there is a better way. You could write an SQL function around
it:

CREATE FUNCTION int_hours(interval) RETURNS integer
LANGUAGE sql IMMUTABLE STRICT AS
'SELECT
CAST (
(
(
(
EXTRACT(YEARS FROM $1) * 12 + EXTRACT(MONTHS FROM $1)
) * 30 + EXTRACT(DAY FROM $1)
) * 24 + EXTRACT(HOURS FROM $1)
)
AS integer)';

test=> SELECT int_hours('25 months 32 days 24 hours');
int_hours
-----------
18792
(1 row)

Quote:

And what if the interval is longer than a month?
How many day's has an "interval-month"?

30.

Without knowing which month it is, you cannot tell its accurate length.
That is an inherent problem of an interval data type.

There is an instructive comment in include/utils/timestamp.h:
/*
* DAYS_PER_MONTH is very imprecise. The more accurate value is
* 365.2425/12 = 30.436875, or '30 days 10:29:06'. Right now we only
* return an integral number of days, but someday perhaps we should
* also return a 'time' value to be used as well. ISO 8601 suggests
* 30 days.
*/
#define DAYS_PER_MONTH 30 /* assumes exactly 30 days per month */

Yours,
Laurenz Albe

#7

Patrick Scharrenberg <pittipatti (AT) web (DOT) de> wrote:

Quote:

I'd like to represent an interval like '1 day 1h 2m 12s' in days, in
this case 25

I assume you mean "hours".

Quote:

Apparently

SELECT to_char(interval '1 day 1h 2m 12s', ' HH24');

returns only one hour.

I read that HH24 can return more than 24 hours, which it does with e.g.:
SELECT to_char(interval '25h 2m 12s', ' HH24');

Internally, an interval is stored as a time value plus a number of days
and months (see include/utils/timestamp.h)
Months are automatically converted to years and months, but days are
never converted to months or hours and vice versa:

test=> SELECT '26 months 3 days'::interval;
interval
-----------------------
2 years 2 mons 3 days
(1 row)

test=> SELECT '25 months 33 days'::interval;
interval
-----------------------
2 years 1 mon 33 days
(1 row)

test=> SELECT '25 months 32 days 24 hours'::interval;
interval
--------------------------------
2 years 1 mon 32 days 24:00:00
(1 row)

Quote:

Is there a more convenient way of returning the number of hours than
with a contruct like the following?
SELECT EXTRACT( DAY FROM interval '1 day 25h 2m 12s')*24 + EXTRACT(
HOUR FROM interval '1 day 25h 2m 12s');

I don't think there is a better way. You could write an SQL function around
it:

CREATE FUNCTION int_hours(interval) RETURNS integer
LANGUAGE sql IMMUTABLE STRICT AS
'SELECT
CAST (
(
(
(
EXTRACT(YEARS FROM $1) * 12 + EXTRACT(MONTHS FROM $1)
) * 30 + EXTRACT(DAY FROM $1)
) * 24 + EXTRACT(HOURS FROM $1)
)
AS integer)';

test=> SELECT int_hours('25 months 32 days 24 hours');
int_hours
-----------
18792
(1 row)

Quote:

And what if the interval is longer than a month?
How many day's has an "interval-month"?

30.

Without knowing which month it is, you cannot tell its accurate length.
That is an inherent problem of an interval data type.

There is an instructive comment in include/utils/timestamp.h:
/*
* DAYS_PER_MONTH is very imprecise. The more accurate value is
* 365.2425/12 = 30.436875, or '30 days 10:29:06'. Right now we only
* return an integral number of days, but someday perhaps we should
* also return a 'time' value to be used as well. ISO 8601 suggests
* 30 days.
*/
#define DAYS_PER_MONTH 30 /* assumes exactly 30 days per month */

Yours,
Laurenz Albe

#8

Patrick Scharrenberg <pittipatti (AT) web (DOT) de> wrote:

Quote:

I'd like to represent an interval like '1 day 1h 2m 12s' in days, in
this case 25

I assume you mean "hours".

Quote:

Apparently

SELECT to_char(interval '1 day 1h 2m 12s', ' HH24');

returns only one hour.

I read that HH24 can return more than 24 hours, which it does with e.g.:
SELECT to_char(interval '25h 2m 12s', ' HH24');

Internally, an interval is stored as a time value plus a number of days
and months (see include/utils/timestamp.h)
Months are automatically converted to years and months, but days are
never converted to months or hours and vice versa:

test=> SELECT '26 months 3 days'::interval;
interval
-----------------------
2 years 2 mons 3 days
(1 row)

test=> SELECT '25 months 33 days'::interval;
interval
-----------------------
2 years 1 mon 33 days
(1 row)

test=> SELECT '25 months 32 days 24 hours'::interval;
interval
--------------------------------
2 years 1 mon 32 days 24:00:00
(1 row)

Quote:

Is there a more convenient way of returning the number of hours than
with a contruct like the following?
SELECT EXTRACT( DAY FROM interval '1 day 25h 2m 12s')*24 + EXTRACT(
HOUR FROM interval '1 day 25h 2m 12s');

I don't think there is a better way. You could write an SQL function around
it:

CREATE FUNCTION int_hours(interval) RETURNS integer
LANGUAGE sql IMMUTABLE STRICT AS
'SELECT
CAST (
(
(
(
EXTRACT(YEARS FROM $1) * 12 + EXTRACT(MONTHS FROM $1)
) * 30 + EXTRACT(DAY FROM $1)
) * 24 + EXTRACT(HOURS FROM $1)
)
AS integer)';

test=> SELECT int_hours('25 months 32 days 24 hours');
int_hours
-----------
18792
(1 row)

Quote:

And what if the interval is longer than a month?
How many day's has an "interval-month"?

30.

Without knowing which month it is, you cannot tell its accurate length.
That is an inherent problem of an interval data type.

There is an instructive comment in include/utils/timestamp.h:
/*
* DAYS_PER_MONTH is very imprecise. The more accurate value is
* 365.2425/12 = 30.436875, or '30 days 10:29:06'. Right now we only
* return an integral number of days, but someday perhaps we should
* also return a 'time' value to be used as well. ISO 8601 suggests
* 30 days.
*/
#define DAYS_PER_MONTH 30 /* assumes exactly 30 days per month */

Yours,
Laurenz Albe

#9

Patrick Scharrenberg <pittipatti (AT) web (DOT) de> wrote:

Quote:

I'd like to represent an interval like '1 day 1h 2m 12s' in days, in
this case 25

I assume you mean "hours".

Quote:

Apparently

SELECT to_char(interval '1 day 1h 2m 12s', ' HH24');

returns only one hour.

I read that HH24 can return more than 24 hours, which it does with e.g.:
SELECT to_char(interval '25h 2m 12s', ' HH24');

Internally, an interval is stored as a time value plus a number of days
and months (see include/utils/timestamp.h)
Months are automatically converted to years and months, but days are
never converted to months or hours and vice versa:

test=> SELECT '26 months 3 days'::interval;
interval
-----------------------
2 years 2 mons 3 days
(1 row)

test=> SELECT '25 months 33 days'::interval;
interval
-----------------------
2 years 1 mon 33 days
(1 row)

test=> SELECT '25 months 32 days 24 hours'::interval;
interval
--------------------------------
2 years 1 mon 32 days 24:00:00
(1 row)

Quote:

Is there a more convenient way of returning the number of hours than
with a contruct like the following?
SELECT EXTRACT( DAY FROM interval '1 day 25h 2m 12s')*24 + EXTRACT(
HOUR FROM interval '1 day 25h 2m 12s');

I don't think there is a better way. You could write an SQL function around
it:

CREATE FUNCTION int_hours(interval) RETURNS integer
LANGUAGE sql IMMUTABLE STRICT AS
'SELECT
CAST (
(
(
(
EXTRACT(YEARS FROM $1) * 12 + EXTRACT(MONTHS FROM $1)
) * 30 + EXTRACT(DAY FROM $1)
) * 24 + EXTRACT(HOURS FROM $1)
)
AS integer)';

test=> SELECT int_hours('25 months 32 days 24 hours');
int_hours
-----------
18792
(1 row)

Quote:

And what if the interval is longer than a month?
How many day's has an "interval-month"?

30.

Without knowing which month it is, you cannot tell its accurate length.
That is an inherent problem of an interval data type.

There is an instructive comment in include/utils/timestamp.h:
/*
* DAYS_PER_MONTH is very imprecise. The more accurate value is
* 365.2425/12 = 30.436875, or '30 days 10:29:06'. Right now we only
* return an integral number of days, but someday perhaps we should
* also return a 'time' value to be used as well. ISO 8601 suggests
* 30 days.
*/
#define DAYS_PER_MONTH 30 /* assumes exactly 30 days per month */

Yours,
Laurenz Albe

#10

I wrote:

Quote:

And what if the interval is longer than a month?
How many day's has an "interval-month"?

30.

Without knowing which month it is, you cannot tell its accurate length.
That is an inherent problem of an interval data type.

I am afraid that what I wrote is a little confused, so let me add:

For all practical purposes, it is best to NOT think that a month in an
interval has a certain number of days.
A month is just a month.

An interval really only "assumes" a definite length when added to or
subtracted from a date or timestamp.

So the function to calculate the number of hours that I posted is inherently
flawed:

test=> SELECT int_hours('1 month 2 days 2 hours');
int_hours
-----------
770
(1 row)

test=> SELECT EXTRACT(DAYS FROM i) * 24 + EXTRACT(HOURS FROM i) FROM
test-> (SELECT (current_timestamp + '1 month 2 days 2 hours') -
test(> current_timestamp AS i) AS x;
?column?
----------
794
(1 row)

But there is no better way to calculate the amount of hours in an interval
(in a month the second query will also return 770).

Yours,
Laurenz Albe