Thread: Formatting intervals..
Hi,
Is it possible to customize interval display.
eg,
tradein_clients=# SELECT cast ('10-10-1999'::timestamp - '1-1-1999'::timestamp AS interval);
+----------+
| interval |
+----------+
| 282 days |
+----------+
(1 row)
can i display it in months and days..
regds
mallah.
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
Uz.ytkownik Rajesh Kumar Mallah napisa?:
> Hi,
>
> Is it possible to customize interval display.
>
> eg,
>
> tradein_clients=# SELECT cast ('10-10-1999'::timestamp - '1-1-1999'::timestamp AS interval);
> +----------+
> | interval |
> +----------+
> | 282 days |
> +----------+
> (1 row)
>
> can i display it in months and days..
It doesn't make sense. You don't have whole date, so how long should
month be? 28? 29? 30? 31?
Regards,
Tomasz Myrta
> >
> > Is it possible to customize interval display.
> >
> > eg,
> >
> > tradein_clients=# SELECT cast ('10-10-1999'::timestamp -
'1-1-1999'::timestamp AS interval);
> > +----------+
> > | interval |
> > +----------+
> > | 282 days |
> > +----------+
> > (1 row)
> >
> > can i display it in months and days..
> It doesn't make sense. You don't have whole date, so how long should
> month be? 28? 29? 30? 31?
>
It does. As long the months between January and October are.
So
select age ('10-10-1999'::timestamp , '1-1-1999'::timestamp ) ; age
---------------9 mons 9 days
(1 row)
Regards, Christoph
Yes i realize what your are saying.
its not a valid calculation.
actually my original problem is
that i have to display this duration
in a human friendly way in my website.
shud i divide by 30 and tell the customer
that its approximate ?
but even that sounds amatuerish to me.
hope i will be able to explain the marketing
guys ;-)
regds
mallah.
On Monday 17 Mar 2003 5:26 pm, Tomasz Myrta wrote:
> Uz.ytkownik Rajesh Kumar Mallah napisa?:
> > Hi,
> >
> > Is it possible to customize interval display.
> >
> > eg,
> >
> > tradein_clients=# SELECT cast ('10-10-1999'::timestamp - '1-1-1999'::timestamp AS interval);
> > +----------+
> > | interval |
> > +----------+
> > | 282 days |
> > +----------+
> > (1 row)
> >
> > can i display it in months and days..
> It doesn't make sense. You don't have whole date, so how long should
> month be? 28? 29? 30? 31?
>
> Regards,
> Tomasz Myrta
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
Thank you,
But i am bit confused.
nevertheless i will be able to satisfy my customers.
only last one question,
how the truncate 9 mons 9 days to only month part ie
9 mons ?
(i leave the serious discussion on you and Thomasz)
regds
mallah.
On Monday 17 Mar 2003 6:33 pm, Christoph Haller wrote:
> > >
> > > Is it possible to customize interval display.
> > >
> > > eg,
> > >
> > > tradein_clients=# SELECT cast ('10-10-1999'::timestamp -
> '1-1-1999'::timestamp AS interval);
> > > +----------+
> > > | interval |
> > > +----------+
> > > | 282 days |
> > > +----------+
> > > (1 row)
> > >
> > > can i display it in months and days..
> > It doesn't make sense. You don't have whole date, so how long should
> > month be? 28? 29? 30? 31?
> >
> It does. As long the months between January and October are.
> So
> select age ('10-10-1999'::timestamp , '1-1-1999'::timestamp ) ;
> age
> ---------------
> 9 mons 9 days
> (1 row)
>
> Regards, Christoph
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
>
> only last one question,
> how the truncate 9 mons 9 days to only month part ie
> 9 mons ?
>
select date_part('month',age ('10-10-1999'::timestamp ,
'1-1-1999'::timestamp ));date_part
----------- 9
(1 row)
or
select extract (month from age ('10-10-1999'::timestamp ,
'1-1-1999'::timestamp ));date_part
----------- 9
(1 row)
Regards, Christoph
>>It doesn't make sense. You don't have whole date, so how long should
>>month be? 28? 29? 30? 31?
>>
>
> It does. As long the months between January and October are.
> So
> select age ('10-10-1999'::timestamp , '1-1-1999'::timestamp ) ;
> age
> ---------------
> 9 mons 9 days
> (1 row)
>
> Regards, Christoph
Your example above is related to some real date, so we know how many
days each month have. When using interval - we don't know how many days
each month should have. beacuse interval is unrelated to date.
Tomasz
>
> >>It doesn't make sense. You don't have whole date, so how long should
> >>month be? 28? 29? 30? 31?
> >>
> >
> > It does. As long the months between January and October are.
> > So
> > select age ('10-10-1999'::timestamp , '1-1-1999'::timestamp ) ;
> > age
> > ---------------
> > 9 mons 9 days
> > (1 row)
> >
> Your example above is related to some real date, so we know how many
> days each month have. When using interval - we don't know how many
days
> each month should have. beacuse interval is unrelated to date.
>
Sorry, but it wasn't my example, it was Mallah's.
But of course you're right on "interval is unrelated to date".
Regards, Christoph
Uz.ytkownik Rajesh Kumar Mallah napisa?:
>
> Yes i realize what your are saying.
> its not a valid calculation.
>
> actually my original problem is
> that i have to display this duration
> in a human friendly way in my website.
>
> shud i divide by 30 and tell the customer
> that its approximate ?
>
> but even that sounds amatuerish to me.
> hope i will be able to explain the marketing
> guys ;-)
>
>
>
> regds
> mallah.
What about this:
select age('1970-1-1'::date+your_interval,'1970-1-1'::date);
It should give you best result - the answer is true in 75% cases
(depending on year)
Tomasz
Yes,
I now understand what tomasz is talking . just
bare interval cannot be converted to months/days
we need at least one of the end points.
thanks to both of you.
Regds
Mallah.
On Monday 17 Mar 2003 7:56 pm, Christoph Haller wrote:
> >
> > >>It doesn't make sense. You don't have whole date, so how long should
>
> > >>month be? 28? 29? 30? 31?
> > >>
> > >
> > > It does. As long the months between January and October are.
> > > So
> > > select age ('10-10-1999'::timestamp , '1-1-1999'::timestamp ) ;
> > > age
> > > ---------------
> > > 9 mons 9 days
> > > (1 row)
> > >
> > Your example above is related to some real date, so we know how many
> > days each month have. When using interval - we don't know how many
> days
> > each month should have. beacuse interval is unrelated to date.
> >
> Sorry, but it wasn't my example, it was Mallah's.
> But of course you're right on "interval is unrelated to date".
>
> Regards, Christoph
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
Guys, BTW, a "to_char" function for INTERVAL is on the TODO list. We just haven't had a volunteer to complete it. -- Josh Berkus Aglio Database Solutions San Francisco
--On Monday, March 17, 2003 10:00:17 -0800 Josh Berkus <josh@agliodbs.com> wrote: > Guys, > > BTW, a "to_char" function for INTERVAL is on the TODO list. We just > haven't had a volunteer to complete it. I was looking for the source for this a month or so back, and couldn't find it. I needed similar stuff. If someone could guide me, I **MIGHT** find the round tuit's for it for 7.4. > > -- > Josh Berkus > Aglio Database Solutions > San Francisco > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
On Mon, Mar 17, 2003 at 10:00:17AM -0800, Josh Berkus wrote:
> Guys,
>
> BTW, a "to_char" function for INTERVAL is on the TODO list. We just haven't
> had a volunteer to complete it.
test=# select to_char('3month 15d 4h 10m'::interval, 'DD-Mon HH24:MI:SS'); to_char
-----------------15-Mar 04:10:00
It's evidently described in docs :-). The implementation is not absolutely
perfec (see list archive), but for basic things it's usable.
Karel
-- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/
Karel Zak writes:
> test=# select to_char('3month 15d 4h 10m'::interval, 'DD-Mon HH24:MI:SS');
> to_char
> -----------------
> 15-Mar 04:10:00
This doesn't seem correct. First, you can't make "March" out of "3
months". Second, 3 months, 15 days and some hours after the start of the
year (if that definition were valid, which it isn't) is on March 16.
Third, why do you have to write "HH24"? Surely no one would want to write
out intervals using a 12-hour clock?
--
Peter Eisentraut peter_e@gmx.net
On Fri, Mar 21, 2003 at 11:05:32AM +0100, Peter Eisentraut wrote:
> Karel Zak writes:
>
> > test=# select to_char('3month 15d 4h 10m'::interval, 'DD-Mon HH24:MI:SS');
> > to_char
> > -----------------
> > 15-Mar 04:10:00
>
> This doesn't seem correct. First, you can't make "March" out of "3
> months". Second, 3 months, 15 days and some hours after the start of the
> year (if that definition were valid, which it isn't) is on March 16.
The interval_to_char() calls interval2tm() and from 'tm' creates output likeeach other to_char() function.
You can try to write better interval2tm() for fix it. I haven't time for this now.
> Third, why do you have to write "HH24"? Surely no one would want to write
> out intervals using a 12-hour clock?
select to_char('5d 13h 10m 5s'::interval, 'HH or HH24:MI:SS'); to_char
----------------01 or 13:10:05 Karel
-- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/