pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFormatting intervals..
15+ messages / 7 participants
[nested] [flat]
* Formatting intervals..
@ 2003-03-17 11:47 Rajesh Kumar Mallah <mallah@trade-india.com>
2003-03-17 11:56 ` Re: Formatting intervals.. Tomasz Myrta <jasiek@klaster.net>
0 siblings, 1 reply; 15+ messages in thread
From: Rajesh Kumar Mallah @ 2003-03-17 11:47 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Formatting intervals..
2003-03-17 11:47 Formatting intervals.. Rajesh Kumar Mallah <mallah@trade-india.com>
@ 2003-03-17 11:56 ` Tomasz Myrta <jasiek@klaster.net>
2003-03-17 13:04 ` Re: Formatting intervals.. Rajesh Kumar Mallah <mallah@trade-india.com>
0 siblings, 1 reply; 15+ messages in thread
From: Tomasz Myrta @ 2003-03-17 11:56 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Formatting intervals..
2003-03-17 11:47 Formatting intervals.. Rajesh Kumar Mallah <mallah@trade-india.com>
2003-03-17 11:56 ` Re: Formatting intervals.. Tomasz Myrta <jasiek@klaster.net>
@ 2003-03-17 13:04 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2003-03-17 14:32 ` Re: Formatting intervals.. Tomasz Myrta <jasiek@klaster.net>
0 siblings, 1 reply; 15+ messages in thread
From: Rajesh Kumar Mallah @ 2003-03-17 13:04 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Formatting intervals..
2003-03-17 11:47 Formatting intervals.. Rajesh Kumar Mallah <mallah@trade-india.com>
2003-03-17 11:56 ` Re: Formatting intervals.. Tomasz Myrta <jasiek@klaster.net>
2003-03-17 13:04 ` Re: Formatting intervals.. Rajesh Kumar Mallah <mallah@trade-india.com>
@ 2003-03-17 14:32 ` Tomasz Myrta <jasiek@klaster.net>
0 siblings, 0 replies; 15+ messages in thread
From: Tomasz Myrta @ 2003-03-17 14:32 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Formatting intervals..
@ 2003-03-17 13:03 Christoph Haller <ch@rodos.fzk.de>
2003-03-17 14:02 ` Re: Formatting intervals.. Rajesh Kumar Mallah <mallah@trade-india.com>
2003-03-17 14:15 ` Re: Formatting intervals.. Tomasz Myrta <jasiek@klaster.net>
0 siblings, 2 replies; 15+ messages in thread
From: Christoph Haller @ 2003-03-17 13:03 UTC (permalink / raw)
To: mallah@trade-india.com; +Cc: jasiek@klaster.net; pgsql-sql
> >
> > 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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Formatting intervals..
2003-03-17 13:03 Re: Formatting intervals.. Christoph Haller <ch@rodos.fzk.de>
@ 2003-03-17 14:02 ` Rajesh Kumar Mallah <mallah@trade-india.com>
1 sibling, 0 replies; 15+ messages in thread
From: Rajesh Kumar Mallah @ 2003-03-17 14:02 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: jasiek@klaster.net; pgsql-sql
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.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Formatting intervals..
2003-03-17 13:03 Re: Formatting intervals.. Christoph Haller <ch@rodos.fzk.de>
@ 2003-03-17 14:15 ` Tomasz Myrta <jasiek@klaster.net>
1 sibling, 0 replies; 15+ messages in thread
From: Tomasz Myrta @ 2003-03-17 14:15 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: mallah@trade-india.com; pgsql-sql
>>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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Formatting intervals..
@ 2003-03-17 14:09 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 15+ messages in thread
From: Christoph Haller @ 2003-03-17 14:09 UTC (permalink / raw)
To: pgsql-sql; +Cc: mallah@trade-india.com
>
> 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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Formatting intervals..
@ 2003-03-17 14:26 Christoph Haller <ch@rodos.fzk.de>
2003-03-17 14:54 ` Re: Formatting intervals.. Rajesh Kumar Mallah <mallah@trade-india.com>
0 siblings, 1 reply; 15+ messages in thread
From: Christoph Haller @ 2003-03-17 14:26 UTC (permalink / raw)
To: pgsql-sql; +Cc: jasiek@klaster.net
>
> >>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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Formatting intervals..
2003-03-17 14:26 Re: Formatting intervals.. Christoph Haller <ch@rodos.fzk.de>
@ 2003-03-17 14:54 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2003-03-17 18:00 ` Re: Formatting intervals.. Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 15+ messages in thread
From: Rajesh Kumar Mallah @ 2003-03-17 14:54 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; pgsql-sql; +Cc: jasiek@klaster.net
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.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Formatting intervals..
2003-03-17 14:26 Re: Formatting intervals.. Christoph Haller <ch@rodos.fzk.de>
2003-03-17 14:54 ` Re: Formatting intervals.. Rajesh Kumar Mallah <mallah@trade-india.com>
@ 2003-03-17 18:00 ` Josh Berkus <josh@agliodbs.com>
2003-03-17 18:02 ` Re: Formatting intervals.. Larry Rosenman <ler@lerctr.org>
2003-03-18 07:44 ` Re: Formatting intervals.. Karel Zak <zakkr@zf.jcu.cz>
0 siblings, 2 replies; 15+ messages in thread
From: Josh Berkus @ 2003-03-17 18:00 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah@trade-india.com>; Christoph Haller <ch@rodos.fzk.de>; pgsql-sql; +Cc: jasiek@klaster.net
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Formatting intervals..
2003-03-17 14:26 Re: Formatting intervals.. Christoph Haller <ch@rodos.fzk.de>
2003-03-17 14:54 ` Re: Formatting intervals.. Rajesh Kumar Mallah <mallah@trade-india.com>
2003-03-17 18:00 ` Re: Formatting intervals.. Josh Berkus <josh@agliodbs.com>
@ 2003-03-17 18:02 ` Larry Rosenman <ler@lerctr.org>
1 sibling, 0 replies; 15+ messages in thread
From: Larry Rosenman @ 2003-03-17 18:02 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; Rajesh Kumar Mallah <mallah@trade-india.com>; Christoph Haller <ch@rodos.fzk.de>; pgsql-sql; +Cc: jasiek@klaster.net
--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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Formatting intervals..
2003-03-17 14:26 Re: Formatting intervals.. Christoph Haller <ch@rodos.fzk.de>
2003-03-17 14:54 ` Re: Formatting intervals.. Rajesh Kumar Mallah <mallah@trade-india.com>
2003-03-17 18:00 ` Re: Formatting intervals.. Josh Berkus <josh@agliodbs.com>
@ 2003-03-18 07:44 ` Karel Zak <zakkr@zf.jcu.cz>
2003-03-21 10:05 ` Re: Formatting intervals.. Peter Eisentraut <peter_e@gmx.net>
1 sibling, 1 reply; 15+ messages in thread
From: Karel Zak @ 2003-03-18 07:44 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Rajesh Kumar Mallah <mallah@trade-india.com>; Christoph Haller <ch@rodos.fzk.de>; pgsql-sql; jasiek@klaster.net
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/
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Formatting intervals..
2003-03-17 14:26 Re: Formatting intervals.. Christoph Haller <ch@rodos.fzk.de>
2003-03-17 14:54 ` Re: Formatting intervals.. Rajesh Kumar Mallah <mallah@trade-india.com>
2003-03-17 18:00 ` Re: Formatting intervals.. Josh Berkus <josh@agliodbs.com>
2003-03-18 07:44 ` Re: Formatting intervals.. Karel Zak <zakkr@zf.jcu.cz>
@ 2003-03-21 10:05 ` Peter Eisentraut <peter_e@gmx.net>
2003-03-21 10:30 ` Re: Formatting intervals.. Karel Zak <zakkr@zf.jcu.cz>
0 siblings, 1 reply; 15+ messages in thread
From: Peter Eisentraut @ 2003-03-21 10:05 UTC (permalink / raw)
To: Karel Zak <zakkr@zf.jcu.cz>; +Cc: Josh Berkus <josh@agliodbs.com>; Rajesh Kumar Mallah <mallah@trade-india.com>; Christoph Haller <ch@rodos.fzk.de>; pgsql-sql; jasiek@klaster.net
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Formatting intervals..
2003-03-17 14:26 Re: Formatting intervals.. Christoph Haller <ch@rodos.fzk.de>
2003-03-17 14:54 ` Re: Formatting intervals.. Rajesh Kumar Mallah <mallah@trade-india.com>
2003-03-17 18:00 ` Re: Formatting intervals.. Josh Berkus <josh@agliodbs.com>
2003-03-18 07:44 ` Re: Formatting intervals.. Karel Zak <zakkr@zf.jcu.cz>
2003-03-21 10:05 ` Re: Formatting intervals.. Peter Eisentraut <peter_e@gmx.net>
@ 2003-03-21 10:30 ` Karel Zak <zakkr@zf.jcu.cz>
0 siblings, 0 replies; 15+ messages in thread
From: Karel Zak @ 2003-03-21 10:30 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Josh Berkus <josh@agliodbs.com>; Rajesh Kumar Mallah <mallah@trade-india.com>; Christoph Haller <ch@rodos.fzk.de>; pgsql-sql; jasiek@klaster.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 like
each 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/
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2003-03-21 10:30 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-17 11:47 Formatting intervals.. Rajesh Kumar Mallah <mallah@trade-india.com>
2003-03-17 11:56 ` Tomasz Myrta <jasiek@klaster.net>
2003-03-17 13:04 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2003-03-17 14:32 ` Tomasz Myrta <jasiek@klaster.net>
2003-03-17 13:03 Re: Formatting intervals.. Christoph Haller <ch@rodos.fzk.de>
2003-03-17 14:02 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2003-03-17 14:15 ` Tomasz Myrta <jasiek@klaster.net>
2003-03-17 14:09 Re: Formatting intervals.. Christoph Haller <ch@rodos.fzk.de>
2003-03-17 14:26 Re: Formatting intervals.. Christoph Haller <ch@rodos.fzk.de>
2003-03-17 14:54 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2003-03-17 18:00 ` Josh Berkus <josh@agliodbs.com>
2003-03-17 18:02 ` Larry Rosenman <ler@lerctr.org>
2003-03-18 07:44 ` Karel Zak <zakkr@zf.jcu.cz>
2003-03-21 10:05 ` Peter Eisentraut <peter_e@gmx.net>
2003-03-21 10:30 ` Karel Zak <zakkr@zf.jcu.cz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox