Thread: Formatting intervals..

Formatting intervals..

From
Rajesh Kumar Mallah
Date:
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.


Re: Formatting intervals..

From
Tomasz Myrta
Date:
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



Re: Formatting intervals..

From
Christoph Haller
Date:
> >
> > 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




Re: Formatting intervals..

From
Rajesh Kumar Mallah
Date:

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.


Re: Formatting intervals..

From
Rajesh Kumar Mallah
Date:

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.


Re: Formatting intervals..

From
Christoph Haller
Date:
>
> 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




Re: Formatting intervals..

From
Tomasz Myrta
Date:
>>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



Re: Formatting intervals..

From
Christoph Haller
Date:
>
> >>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




Re: Formatting intervals..

From
Tomasz Myrta
Date:
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




Re: Formatting intervals..

From
Rajesh Kumar Mallah
Date:

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.


Re: Formatting intervals..

From
Josh Berkus
Date:
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


Re: Formatting intervals..

From
Larry Rosenman
Date:

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





Re: Formatting intervals..

From
Karel Zak
Date:
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/


Re: Formatting intervals..

From
Peter Eisentraut
Date:
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



Re: Formatting intervals..

From
Karel Zak
Date:
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/