I have the need to output intervals (ages in this case).
PostgreSQL takes great care to handle months correctly (eg
take into account varying months lengths). This is only
possible if either end point or start point of an interval are
known. For post processing some of the ambiguity of what
"2 mons" means would be removed if "61 days" was returned.
Is there a way to tell PostgreSQL to return that type of
interval (eg use weeks, days, hours, minutes, seconds, ...
but not months and perhaps not even years [leap years, etc]) ?
to_char(interval, text) doesn't work as it is applied after
the fact.
Karsten

GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
(end of broadcast)
TIP 7: don't forget to increase your free space map settings 10 2107
On Tue, May 04, 2004 at 12:24:37 +0200,
Karsten Hilbert <Ka*************@gmx.net> wrote: I have the need to output intervals (ages in this case). PostgreSQL takes great care to handle months correctly (eg take into account varying months lengths). This is only possible if either end point or start point of an interval are known. For post processing some of the ambiguity of what "2 mons" means would be removed if "61 days" was returned.
This is sort of done now, but the months part of the interval will be
treated as 30 days.
Is there a way to tell PostgreSQL to return that type of interval (eg use weeks, days, hours, minutes, seconds, ... but not months and perhaps not even years [leap years, etc]) ? to_char(interval, text) doesn't work as it is applied after the fact.
You can extract "epoch" from the interval to get the total number of
seconds in the interval (converting months to the number of seconds
in 30 days) and then divide that by the appropiate amount.
(end of broadcast)
TIP 7: don't forget to increase your free space map settings
On Tue, May 04, 2004 at 12:24:37 +0200,
Karsten Hilbert <Ka*************@gmx.net> wrote: I have the need to output intervals (ages in this case). PostgreSQL takes great care to handle months correctly (eg take into account varying months lengths). This is only possible if either end point or start point of an interval are known. For post processing some of the ambiguity of what "2 mons" means would be removed if "61 days" was returned.
This is sort of done now, but the months part of the interval will be
treated as 30 days.
Is there a way to tell PostgreSQL to return that type of interval (eg use weeks, days, hours, minutes, seconds, ... but not months and perhaps not even years [leap years, etc]) ? to_char(interval, text) doesn't work as it is applied after the fact.
You can extract "epoch" from the interval to get the total number of
seconds in the interval (converting months to the number of seconds
in 30 days) and then divide that by the appropiate amount.
(end of broadcast)
TIP 7: don't forget to increase your free space map settings
Bruno,
thanks for answering. I still have some questions: I have the need to output intervals (ages in this case). PostgreSQL takes great care to handle months correctly (eg take into account varying months lengths). This is only possible if either end point or start point of an interval are known. For post processing some of the ambiguity of what "2 mons" means would be removed if "61 days" was returned. This is sort of done now, but the months part of the interval will be treated as 30 days.
Are you saying that when PostgreSQL returns "... 3 mons ..."
as a representation of an interval I can safely assume that
when it calculated the number of months it used 30 days
regardless of the actual length of the month ? I couldn't find
that number mentioned anywhere and had not browsed the source
yet. That would also be contrary to what I thought. I assumed
the following would happen:
select age('199922', '199932');
select age('199952', '199962');
would both return "1 mon" (despite the first one being 28 days
and the second one being 31 days).
I am now looking for a way to say:
select age('199922', '199932', without months);
select age('199952', '199962', without months);
and get "28 days" in the first and "31 days" in the second
result.
However, if you say that "1 mon" is always considered 30 days
in this context I would expect to receive:
1) "1 mon 2 days" (it would return 28 days of course, I know)
2) "1 mon 1 day"
Neither 7.1 nor 7.4 return that.
You can extract "epoch" from the interval to get the total number of seconds in the interval (converting months to the number of seconds in 30 days) and then divide that by the appropiate amount.
That only works if the above holds true, eg the month must be
fixed to 30 days by the calculation *generating* the interval
representation. Applying epoch *after* the fact is no good,
does it, because the epoch() code won't know whether "1 mons"
is to be 28 or 29 or 30 or 31 days.
Am I missing something here ?
Karsten

GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
(end of broadcast)
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org
Bruno,
thanks for answering. I still have some questions: I have the need to output intervals (ages in this case). PostgreSQL takes great care to handle months correctly (eg take into account varying months lengths). This is only possible if either end point or start point of an interval are known. For post processing some of the ambiguity of what "2 mons" means would be removed if "61 days" was returned. This is sort of done now, but the months part of the interval will be treated as 30 days.
Are you saying that when PostgreSQL returns "... 3 mons ..."
as a representation of an interval I can safely assume that
when it calculated the number of months it used 30 days
regardless of the actual length of the month ? I couldn't find
that number mentioned anywhere and had not browsed the source
yet. That would also be contrary to what I thought. I assumed
the following would happen:
select age('199922', '199932');
select age('199952', '199962');
would both return "1 mon" (despite the first one being 28 days
and the second one being 31 days).
I am now looking for a way to say:
select age('199922', '199932', without months);
select age('199952', '199962', without months);
and get "28 days" in the first and "31 days" in the second
result.
However, if you say that "1 mon" is always considered 30 days
in this context I would expect to receive:
1) "1 mon 2 days" (it would return 28 days of course, I know)
2) "1 mon 1 day"
Neither 7.1 nor 7.4 return that.
You can extract "epoch" from the interval to get the total number of seconds in the interval (converting months to the number of seconds in 30 days) and then divide that by the appropiate amount.
That only works if the above holds true, eg the month must be
fixed to 30 days by the calculation *generating* the interval
representation. Applying epoch *after* the fact is no good,
does it, because the epoch() code won't know whether "1 mons"
is to be 28 or 29 or 30 or 31 days.
Am I missing something here ?
Karsten

GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
(end of broadcast)
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org
On Tue, May 04, 2004 at 22:59:34 +0200,
Karsten Hilbert <Ka*************@gmx.net> wrote: Are you saying that when PostgreSQL returns "... 3 mons ..." as a representation of an interval I can safely assume that when it calculated the number of months it used 30 days regardless of the actual length of the month ? I couldn't find
It only does this when there is no month to know the length of.
Offhand the only way I know of to get this is to extract the
epoch part of a month which combines the month/year part of the interval
with the week/day/hour/minute/second part without knowing which particular
months are being referred to.
that number mentioned anywhere and had not browsed the source yet. That would also be contrary to what I thought. I assumed the following would happen:
select age('199922', '199932'); select age('199952', '199962');
would both return "1 mon" (despite the first one being 28 days and the second one being 31 days).
No it doesn't do that. In those examples it knows what particular months
are involved and can use the correct length.
I am now looking for a way to say:
select age('199922', '199932', without months); select age('199952', '199962', without months);
and get "28 days" in the first and "31 days" in the second result.
select '199932'::date  '199922'::date;
select '199962'::date  '199952'::date; However, if you say that "1 mon" is always considered 30 days in this context I would expect to receive:
That isn't what I said and that isn't what happens. 1) "1 mon 2 days" (it would return 28 days of course, I know) 2) "1 mon 1 day"
Neither 7.1 nor 7.4 return that.
You can extract "epoch" from the interval to get the total number of seconds in the interval (converting months to the number of seconds in 30 days) and then divide that by the appropiate amount. That only works if the above holds true, eg the month must be fixed to 30 days by the calculation *generating* the interval representation. Applying epoch *after* the fact is no good, does it, because the epoch() code won't know whether "1 mons" is to be 28 or 29 or 30 or 31 days.
Not exactly. Months are converted to 30 days in the above situation, but
not always.
Am I missing something here ?
Note that intervals store two different values in them. One is a time in
months and another is in some multiple (possibly 1) of seconds. Often one
or the other of these is zero, but not always.
(end of broadcast)
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)
On Tue, May 04, 2004 at 22:59:34 +0200,
Karsten Hilbert <Ka*************@gmx.net> wrote: Are you saying that when PostgreSQL returns "... 3 mons ..." as a representation of an interval I can safely assume that when it calculated the number of months it used 30 days regardless of the actual length of the month ? I couldn't find
It only does this when there is no month to know the length of.
Offhand the only way I know of to get this is to extract the
epoch part of a month which combines the month/year part of the interval
with the week/day/hour/minute/second part without knowing which particular
months are being referred to.
that number mentioned anywhere and had not browsed the source yet. That would also be contrary to what I thought. I assumed the following would happen:
select age('199922', '199932'); select age('199952', '199962');
would both return "1 mon" (despite the first one being 28 days and the second one being 31 days).
No it doesn't do that. In those examples it knows what particular months
are involved and can use the correct length.
I am now looking for a way to say:
select age('199922', '199932', without months); select age('199952', '199962', without months);
and get "28 days" in the first and "31 days" in the second result.
select '199932'::date  '199922'::date;
select '199962'::date  '199952'::date; However, if you say that "1 mon" is always considered 30 days in this context I would expect to receive:
That isn't what I said and that isn't what happens. 1) "1 mon 2 days" (it would return 28 days of course, I know) 2) "1 mon 1 day"
Neither 7.1 nor 7.4 return that.
You can extract "epoch" from the interval to get the total number of seconds in the interval (converting months to the number of seconds in 30 days) and then divide that by the appropiate amount. That only works if the above holds true, eg the month must be fixed to 30 days by the calculation *generating* the interval representation. Applying epoch *after* the fact is no good, does it, because the epoch() code won't know whether "1 mons" is to be 28 or 29 or 30 or 31 days.
Not exactly. Months are converted to 30 days in the above situation, but
not always.
Am I missing something here ?
Note that intervals store two different values in them. One is a time in
months and another is in some multiple (possibly 1) of seconds. Often one
or the other of these is zero, but not always.
(end of broadcast)
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)
Karsten Hilbert <Ka*************@gmx.net> writes: I am now looking for a way to say: select age('199922', '199932', without months); select age('199952', '199962', without months); and get "28 days" in the first and "31 days" in the second result.
Just subtract the two timestamps (or dates) instead of using age().
Then you get an interval that has no month component.
However, if you say that "1 mon" is always considered 30 days
He didn't say that. He said that when the system *must* convert a
monthbased interval to days and it has no date reference for it,
it uses 30 days. Something like "now() + '1 month'::interval"
will do the "right thing". This IMHO is the main application of
intervals with month components ...
regards, tom lane
(end of broadcast)
TIP 4: Don't 'kill 9' the postmaster
Karsten Hilbert <Ka*************@gmx.net> writes: I am now looking for a way to say: select age('199922', '199932', without months); select age('199952', '199962', without months); and get "28 days" in the first and "31 days" in the second result.
Just subtract the two timestamps (or dates) instead of using age().
Then you get an interval that has no month component.
However, if you say that "1 mon" is always considered 30 days
He didn't say that. He said that when the system *must* convert a
monthbased interval to days and it has no date reference for it,
it uses 30 days. Something like "now() + '1 month'::interval"
will do the "right thing". This IMHO is the main application of
intervals with month components ...
regards, tom lane
(end of broadcast)
TIP 4: Don't 'kill 9' the postmaster
Tom, Just subtract the two timestamps (or dates) instead of using age(). Then you get an interval that has no month component.
*That* was what I was looking for. Thanks !
He didn't say that. He said that when the system *must* convert a monthbased interval to days and it has no date reference for it, it uses 30 days. Something like "now() + '1 month'::interval" will do the "right thing". This IMHO is the main application of intervals with month components ...
I knew PostgreSQL would do the Right Thing(tm) where possible
and assume reasonable defaults where ambiguity exists. I just
didn't know how to tell it to return the right version of the
Right Thing.
As usual, sage advice.
Karsten

GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
(end of broadcast)
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribenomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly
Tom, Just subtract the two timestamps (or dates) instead of using age(). Then you get an interval that has no month component.
*That* was what I was looking for. Thanks !
He didn't say that. He said that when the system *must* convert a monthbased interval to days and it has no date reference for it, it uses 30 days. Something like "now() + '1 month'::interval" will do the "right thing". This IMHO is the main application of intervals with month components ...
I knew PostgreSQL would do the Right Thing(tm) where possible
and assume reasonable defaults where ambiguity exists. I just
didn't know how to tell it to return the right version of the
Right Thing.
As usual, sage advice.
Karsten

GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
(end of broadcast)
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribenomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly This discussion thread is closed Replies have been disabled for this discussion. Similar topics
4 posts
views
Thread by Claudia Fong 
last post: by

6 posts
views
Thread by SJ 
last post: by

4 posts
views
Thread by ziondreams 
last post: by

reply
views
Thread by Karsten Hilbert 
last post: by

reply
views
Thread by Ian E. Morgan 
last post: by

2 posts
views
Thread by pruebauno 
last post: by

3 posts
views
Thread by alex.fishman 
last post: by

2 posts
views
Thread by JP SIngh 
last post: by

1 post
views
Thread by russ 
last post: by
          