pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDoes anyone use TO_CHAR(INTERVAL)?
8+ messages / 5 participants
[nested] [flat]
* Does anyone use TO_CHAR(INTERVAL)?
@ 2003-03-25 17:31 Josh Berkus <josh@agliodbs.com>
2003-03-25 19:24 ` Re: Does anyone use TO_CHAR(INTERVAL)? Tomasz Myrta <jasiek@klaster.net>
2003-03-26 07:10 ` Re: Does anyone use TO_CHAR(INTERVAL)? Karel Zak <zakkr@zf.jcu.cz>
0 siblings, 2 replies; 8+ messages in thread
From: Josh Berkus @ 2003-03-25 17:31 UTC (permalink / raw)
To: pgsql-sql
Folks,
We're discussing the behavior of the current "to_char(interval)" function on
HACKERS. Does anyone on this list use the current behavior? Please speak
up, as we're trying to figure out what backward compatibility needs to be
maintained.
PLEASE NOTE: This is only to_char() for INTERVAL values, NOT for dates,
timestamp, or numeric!
Thanks!
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Does anyone use TO_CHAR(INTERVAL)?
2003-03-25 17:31 Does anyone use TO_CHAR(INTERVAL)? Josh Berkus <josh@agliodbs.com>
@ 2003-03-25 19:24 ` Tomasz Myrta <jasiek@klaster.net>
2003-03-26 04:43 ` Re: Does anyone use TO_CHAR(INTERVAL)? Bruno Wolff III <bruno@wolff.to>
1 sibling, 1 reply; 8+ messages in thread
From: Tomasz Myrta @ 2003-03-25 19:24 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
Uz.ytkownik Josh Berkus napisa?:
> Folks,
>
> We're discussing the behavior of the current "to_char(interval)" function on
> HACKERS. Does anyone on this list use the current behavior? Please speak
> up, as we're trying to figure out what backward compatibility needs to be
> maintained.
>
> PLEASE NOTE: This is only to_char() for INTERVAL values, NOT for dates,
> timestamp, or numeric!
In my projects intervals are always related to some dates when
displaying them, so I use to_char(date+interval).
It looks it could be useful to display how much time is left for
scheduling cases, but as I wrote few threads ago - displaying intervals
longer than one month is useless without specific date.
Anyway, maybe it should be left for future use, but without patterns
related to date, or maybe with DDD only?
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Does anyone use TO_CHAR(INTERVAL)?
2003-03-25 17:31 Does anyone use TO_CHAR(INTERVAL)? Josh Berkus <josh@agliodbs.com>
2003-03-25 19:24 ` Re: Does anyone use TO_CHAR(INTERVAL)? Tomasz Myrta <jasiek@klaster.net>
@ 2003-03-26 04:43 ` Bruno Wolff III <bruno@wolff.to>
2003-03-26 08:17 ` Re: Does anyone use TO_CHAR(INTERVAL)? Tomasz Myrta <jasiek@klaster.net>
0 siblings, 1 reply; 8+ messages in thread
From: Bruno Wolff III @ 2003-03-26 04:43 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-sql
On Tue, Mar 25, 2003 at 20:24:55 +0100,
Tomasz Myrta <jasiek@klaster.net> wrote:
> Uz.ytkownik Josh Berkus napisa?:
>
> It looks it could be useful to display how much time is left for
> scheduling cases, but as I wrote few threads ago - displaying intervals
> longer than one month is useless without specific date.
That isn't true. Intervals have two parts. One is time difference in
fixed time and the other is a difference in months. You could display
both parts when they are both nonzero.
I haven't found the documenation too specific on which part gets added
first (and this does make a difference in some cases).
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Does anyone use TO_CHAR(INTERVAL)?
2003-03-25 17:31 Does anyone use TO_CHAR(INTERVAL)? Josh Berkus <josh@agliodbs.com>
2003-03-25 19:24 ` Re: Does anyone use TO_CHAR(INTERVAL)? Tomasz Myrta <jasiek@klaster.net>
2003-03-26 04:43 ` Re: Does anyone use TO_CHAR(INTERVAL)? Bruno Wolff III <bruno@wolff.to>
@ 2003-03-26 08:17 ` Tomasz Myrta <jasiek@klaster.net>
2003-03-26 09:50 ` Re: Does anyone use TO_CHAR(INTERVAL)? Peter Childs <blue.dragon@blueyonder.co.uk>
0 siblings, 1 reply; 8+ messages in thread
From: Tomasz Myrta @ 2003-03-26 08:17 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-sql
Uz.ytkownik Bruno Wolff III napisa?:
> On Tue, Mar 25, 2003 at 20:24:55 +0100,
> Tomasz Myrta <jasiek@klaster.net> wrote:
>
>>Uz.ytkownik Josh Berkus napisa?:
>>
>>It looks it could be useful to display how much time is left for
>>scheduling cases, but as I wrote few threads ago - displaying intervals
>>longer than one month is useless without specific date.
>
>
> That isn't true. Intervals have two parts. One is time difference in
> fixed time and the other is a difference in months. You could display
> both parts when they are both nonzero.
>
> I haven't found the documenation too specific on which part gets added
> first (and this does make a difference in some cases).
It looks like you are right:
SELECT cast('300 days' as interval)+cast('1 month' as interval);;
?column?
----------------
1 mon 300 days
SELECT to_char(cast('300 days' as interval)+cast('1 month' as
interval),'DDD');;
to_char
---------
300
(1 row)
SELECT to_char('2002-12-31'::timestamp
-'2002-01-01'::timestamp,'YYYY-MM-DD');;
to_char
-------------
0000-00-36
(1 row)
Thanks for noticing this, these results are really strange for me.
I should remember NOT to use months when working with intervals. It
looks like intervals contain 2 totally independent values inside.
I have one more question to this thread - Does anyone use intervals
month value?
Tomasz
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Does anyone use TO_CHAR(INTERVAL)?
2003-03-25 17:31 Does anyone use TO_CHAR(INTERVAL)? Josh Berkus <josh@agliodbs.com>
2003-03-25 19:24 ` Re: Does anyone use TO_CHAR(INTERVAL)? Tomasz Myrta <jasiek@klaster.net>
2003-03-26 04:43 ` Re: Does anyone use TO_CHAR(INTERVAL)? Bruno Wolff III <bruno@wolff.to>
2003-03-26 08:17 ` Re: Does anyone use TO_CHAR(INTERVAL)? Tomasz Myrta <jasiek@klaster.net>
@ 2003-03-26 09:50 ` Peter Childs <blue.dragon@blueyonder.co.uk>
2003-03-26 10:05 ` Re: Does anyone use TO_CHAR(INTERVAL)? Tomasz Myrta <jasiek@klaster.net>
0 siblings, 1 reply; 8+ messages in thread
From: Peter Childs @ 2003-03-26 09:50 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
On Wed, 26 Mar 2003, Tomasz Myrta wrote:
>
> Thanks for noticing this, these results are really strange for me.
> I should remember NOT to use months when working with intervals. It
> looks like intervals contain 2 totally independent values inside.
>
> I have one more question to this thread - Does anyone use intervals
> month value?
Since all months are not equal in length how do else do you come
up with an interval that is 1 month since us humans like to use months.
As you note Postgres trys to ovide using Months if at all possible
because 1 month <> 1 month hense.
select (cast('2-2-2003' as date) + cast('1 month' as interval)) -
cast('2-2-2003' as date);
?column?
----------
28 days
(1 row)
but if all you say is 1 month from now how many days you month you mean
will mean a different number of days. Oh one more good one is....
select cast('1 year' as interval) - cast('365 days' as interval);
?column?
------------------
1 year -365 days
(1 row)
It would seam that a year is 12 months but not 365 days which
while this is true (due to leap years) most people would never think like
that. Its seams that postgres will not tell you how many days there are in
a year but this seams even more stupid
select cast('1 month' as interval)/2 * 2;
?column?
----------
360 days
(1 row)
oh and just when you thought somthing was really straght forward you
get...
select cast('1 year' as interval)/24 * 24;
?column?
----------
360 days
(1 row)
This reminds me of a calculator that comes with MS Windows....
How ever the implementation in postgres is the best I've sean so
long as you don't do anything stupid!
Peter Childs
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Does anyone use TO_CHAR(INTERVAL)?
2003-03-25 17:31 Does anyone use TO_CHAR(INTERVAL)? Josh Berkus <josh@agliodbs.com>
2003-03-25 19:24 ` Re: Does anyone use TO_CHAR(INTERVAL)? Tomasz Myrta <jasiek@klaster.net>
2003-03-26 04:43 ` Re: Does anyone use TO_CHAR(INTERVAL)? Bruno Wolff III <bruno@wolff.to>
2003-03-26 08:17 ` Re: Does anyone use TO_CHAR(INTERVAL)? Tomasz Myrta <jasiek@klaster.net>
2003-03-26 09:50 ` Re: Does anyone use TO_CHAR(INTERVAL)? Peter Childs <blue.dragon@blueyonder.co.uk>
@ 2003-03-26 10:05 ` Tomasz Myrta <jasiek@klaster.net>
2003-03-26 16:34 ` Re: Does anyone use TO_CHAR(INTERVAL)? Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 8+ messages in thread
From: Tomasz Myrta @ 2003-03-26 10:05 UTC (permalink / raw)
To: Peter Childs <blue.dragon@blueyonder.co.uk>; +Cc: pgsql-sql
Uz.ytkownik Peter Childs napisa?:
> On Wed, 26 Mar 2003, Tomasz Myrta wrote:
>
>
>>Thanks for noticing this, these results are really strange for me.
>>I should remember NOT to use months when working with intervals. It
>>looks like intervals contain 2 totally independent values inside.
>>
>>I have one more question to this thread - Does anyone use intervals
>>month value?
>
>
> Since all months are not equal in length how do else do you come
> up with an interval that is 1 month since us humans like to use months.
> As you note Postgres trys to ovide using Months if at all possible
> because 1 month <> 1 month hense.
>
> select (cast('2-2-2003' as date) + cast('1 month' as interval)) -
> cast('2-2-2003' as date);
> ?column?
> ----------
> 28 days
> (1 row)
>
> but if all you say is 1 month from now how many days you month you mean
> will mean a different number of days. Oh one more good one is....
>
> select cast('1 year' as interval) - cast('365 days' as interval);
> ?column?
> ------------------
> 1 year -365 days
> (1 row)
>
> It would seam that a year is 12 months but not 365 days which
> while this is true (due to leap years) most people would never think like
> that. Its seams that postgres will not tell you how many days there are in
> a year but this seams even more stupid
>
> select cast('1 month' as interval)/2 * 2;
> ?column?
> ----------
> 360 days
> (1 row)
>
> oh and just when you thought somthing was really straght forward you
> get...
>
> select cast('1 year' as interval)/24 * 24;
> ?column?
> ----------
> 360 days
> (1 row)
>
> This reminds me of a calculator that comes with MS Windows....
> How ever the implementation in postgres is the best I've sean so
> long as you don't do anything stupid!
>
> Peter Childs
I already know all of these things (look at thread "Formatting
intervals" dated on 2003-03-17). I was just thinking about intervals
idea. Keeping time inside interval is good. Keeping months inside
interval also looks good, but keeping both of them doesn't make sense.
It is impossible to use these fields together without referring them to
some real date.
Tomasz Myrta
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Does anyone use TO_CHAR(INTERVAL)?
2003-03-25 17:31 Does anyone use TO_CHAR(INTERVAL)? Josh Berkus <josh@agliodbs.com>
2003-03-25 19:24 ` Re: Does anyone use TO_CHAR(INTERVAL)? Tomasz Myrta <jasiek@klaster.net>
2003-03-26 04:43 ` Re: Does anyone use TO_CHAR(INTERVAL)? Bruno Wolff III <bruno@wolff.to>
2003-03-26 08:17 ` Re: Does anyone use TO_CHAR(INTERVAL)? Tomasz Myrta <jasiek@klaster.net>
2003-03-26 09:50 ` Re: Does anyone use TO_CHAR(INTERVAL)? Peter Childs <blue.dragon@blueyonder.co.uk>
2003-03-26 10:05 ` Re: Does anyone use TO_CHAR(INTERVAL)? Tomasz Myrta <jasiek@klaster.net>
@ 2003-03-26 16:34 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 8+ messages in thread
From: Josh Berkus @ 2003-03-26 16:34 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; Peter Childs <blue.dragon@blueyonder.co.uk>; +Cc: pgsql-sql
Tomasz, Peter,
> I already know all of these things (look at thread "Formatting
> intervals" dated on 2003-03-17). I was just thinking about intervals
> idea. Keeping time inside interval is good. Keeping months inside
> interval also looks good, but keeping both of them doesn't make sense.
> It is impossible to use these fields together without referring them to
> some real date.
However illogical the current bahavior may seem, it *is* the SQL92/99
specification, so we're keeping it.
I personally use INTERVAL heavily for calendaring applications. I got into a
discussion with Thomas Lockhart last year about how INTERVAL treats daylight
savings time -- which is seriously problematic from a calendar designer's
perspective -- and was turned down by the core team wanting to change the
behavior.
Regrettably, even though the ANSI committee made a few mistakes with
DATE/TIME, they *are* the ANSI committee and PostgreSQL as a project is very
firmly committed to standards.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Does anyone use TO_CHAR(INTERVAL)?
2003-03-25 17:31 Does anyone use TO_CHAR(INTERVAL)? Josh Berkus <josh@agliodbs.com>
@ 2003-03-26 07:10 ` Karel Zak <zakkr@zf.jcu.cz>
1 sibling, 0 replies; 8+ messages in thread
From: Karel Zak @ 2003-03-26 07:10 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
On Tue, Mar 25, 2003 at 09:31:27AM -0800, Josh Berkus wrote:
> Folks,
>
> We're discussing the behavior of the current "to_char(interval)" function on
> HACKERS. Does anyone on this list use the current behavior? Please speak
> up, as we're trying to figure out what backward compatibility needs to be
> maintained.
>
> PLEASE NOTE: This is only to_char() for INTERVAL values, NOT for dates,
> timestamp, or numeric!
NOTE (I already said in hackers list): we can mark it as deprecated
in 7.4 and remove it in 7.5. What thinks about it someone from core
team?
Karel
--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2003-03-26 16:34 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-25 17:31 Does anyone use TO_CHAR(INTERVAL)? Josh Berkus <josh@agliodbs.com>
2003-03-25 19:24 ` Tomasz Myrta <jasiek@klaster.net>
2003-03-26 04:43 ` Bruno Wolff III <bruno@wolff.to>
2003-03-26 08:17 ` Tomasz Myrta <jasiek@klaster.net>
2003-03-26 09:50 ` Peter Childs <blue.dragon@blueyonder.co.uk>
2003-03-26 10:05 ` Tomasz Myrta <jasiek@klaster.net>
2003-03-26 16:34 ` Josh Berkus <josh@agliodbs.com>
2003-03-26 07:10 ` 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