Thread: No Documentation for to_char(INTERVAL, mask)
Bruce, Tom, et. al.,I can't find any documentation for what masks to use with the function
TO_CHAR(INTERVAL, mask). Is there a TO_CHAR(INTERVAL)? If so, what
masks are there? If not, how would you suggest I convert an interval
value for user-friendly display?
-Josh Berkus
--
______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete
informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small
businesses fax 621-2533 and non-profit organizations. San Francisco
Did you see: http://www.postgresql.org/users-lounge/docs/7.0/user/functions2872.htm On Mon, 5 Mar 2001, Josh Berkus wrote: > I can't find any documentation for what masks to use with the function > TO_CHAR(INTERVAL, mask). Is there a TO_CHAR(INTERVAL)? If so, what > masks are there? If not, how would you suggest I convert an interval > value for user-friendly display?
On Mon, Mar 05, 2001 at 08:39:05PM -0800, Josh Berkus wrote:
> Bruce, Tom, et. al.,
>
> I can't find any documentation for what masks to use with the function
> TO_CHAR(INTERVAL, mask). Is there a TO_CHAR(INTERVAL)? If so, what
> masks are there? If not, how would you suggest I convert an interval
The 'interval' version of to_char() isn't implemented -- may be in 7.2
(it's high in my TODO list:-)
Karel
-- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/C, PostgreSQL, PHP, WWW, http://docs.linux.cz,
http://mape.jcu.cz
Karel,
> The 'interval' version of to_char() isn't implemented -- may be in 7.2
> (it's high in my TODO list:-)
Grazie. (One of the things I love about PostgreSQL is being able to
get definitive answers on functionality -- try asking Microsoft an "is
this implemented?" question!)
Given the lack of to_char(interval), I'd like to write a PLPGSQL
function to fill the gap in the meantime. If you can answer a few
questions about how interval values work, it would be immensely helpful:
1. Hours, minutes, and seconds are displayed as "00:00:00". Days are
displayed as "0 00:00:00". How are weeks, months, and years displayed?
2. If months have their own placeholder in the Interval data type, how
many days make up a month? Is it a fixed value, or does it depend on
the calendar?
Thanks. I'll post the PLPGSQL function to the list after I write it.
-Josh Berkus
--
______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete
informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small
businesses fax 621-2533 and non-profit organizations. San Francisco
On Tue, Mar 06, 2001 at 09:22:25AM -0800, Josh Berkus wrote:
> Karel,
>
> > The 'interval' version of to_char() isn't implemented -- may be in 7.2
> > (it's high in my TODO list:-)
>
> Grazie. (One of the things I love about PostgreSQL is being able to
> get definitive answers on functionality -- try asking Microsoft an "is
> this implemented?" question!)
:-)
> Given the lack of to_char(interval), I'd like to write a PLPGSQL
> function to fill the gap in the meantime. If you can answer a few
> questions about how interval values work, it would be immensely helpful:
>
> 1. Hours, minutes, and seconds are displayed as "00:00:00". Days are
> displayed as "0 00:00:00". How are weeks, months, and years displayed?
>
> 2. If months have their own placeholder in the Interval data type, how
> many days make up a month? Is it a fixed value, or does it depend on
> the calendar?
A displayed format is external string alternate of a internal number based
form. A interval/timestamp string that you use in SQL is parsed to 'tm'
struct (see man ctime) where has each item like hours, minutes own field.
For some date/time operation is used Julian date (..etc) -- internaly PG
not works with strings for date/time.
I mean is too much difficult write a 'interval' to_char() version in
some procedural language without access to real (internal) form of
'interval'.
Big date/time guru is Thomas (CC:), comments?
Karel
-- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/C, PostgreSQL, PHP, WWW, http://docs.linux.cz,
http://mape.jcu.cz
Thomas, Karel,
> I agree with Karel's point that it may be a pain to use a procedural
> language to manipulate a "stringy" interval value. If you use a C
> function instead, you can get access to the internal manipulation
> functions already present, as well as access to system functions to
> manipulate a tm structure.
Ah, but this leaves out two important considerations of my particular
problem:
1. The interval I want to manipulate is limited to a relative handful of
possibilities (it's a billing interval): 1 week, 2 weeks, 2x/Month, 1
month, 2 months, 3 months.
2. I don't do C. And I don't have the budget to hire somebody to di it
in C.
If this was a bigger budget project, I'd simply take Karel's notes and
hire a programmer to create the to_char(Interval) function and thus
contribute to PostgreSQL ... but this project is over budget and behind
schedule already.
I'll take a stab at in in PLPGSQL and post the results.
-Josh Berkus
--
______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete
informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small
businesses fax 621-2533 and non-profit organizations. San Francisco
> I'll take a stab at in in PLPGSQL and post the results.
OK. date_part() is your friend ;)
- Thomas
> > Given the lack of to_char(interval), I'd like to write a PLPGSQL
> > function to fill the gap in the meantime...
> I mean is too much difficult write a 'interval' to_char() version in
> some procedural language without access to real (internal) form of
> 'interval'.
I agree with Karel's point that it may be a pain to use a procedural
language to manipulate a "stringy" interval value. If you use a C
function instead, you can get access to the internal manipulation
functions already present, as well as access to system functions to
manipulate a tm structure.
A combination of contrib/ and src/backend/utils/adt/ information could
give you a start on the C implementation (and that is rather easily
moved into the backend later).
I haven't tried the PL/PGSQL approach however. If you decide to proceed
on that, let us know how it goes!
- Thomas
On Wed, Mar 07, 2001 at 08:38:25AM -0800, Josh Berkus wrote:
> Thomas, Karel,
>
> > I agree with Karel's point that it may be a pain to use a procedural
> > language to manipulate a "stringy" interval value. If you use a C
> > function instead, you can get access to the internal manipulation
> > functions already present, as well as access to system functions to
> > manipulate a tm structure.
>
> Ah, but this leaves out two important considerations of my particular
> problem:
>
> 1. The interval I want to manipulate is limited to a relative handful of
> possibilities (it's a billing interval): 1 week, 2 weeks, 2x/Month, 1
> month, 2 months, 3 months.
>
> 2. I don't do C. And I don't have the budget to hire somebody to di it
> in C.
>
> If this was a bigger budget project, I'd simply take Karel's notes and
> hire a programmer to create the to_char(Interval) function and thus
> contribute to PostgreSQL ... but this project is over budget and behind
> schedule already.Now I'm not writing to_char(interval), because current source (7.1) is
freeze for new features and I'm waiting for 7.2 devel. cycle and I'm spending
time with other things (PL/Python, the Mape project etc..).
If it's *really important* for you I can write it next week(s),
... of course, my time is limited :-)
May be try found some other solution.
Karel
-- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/C, PostgreSQL, PHP, WWW, http://docs.linux.cz,
http://mape.jcu.cz
karel,
> Now I'm not writing to_char(interval), because current source (7.1)
> is
> freeze for new features and I'm waiting for 7.2 devel. cycle and I'm
> spending
> time with other things (PL/Python, the Mape project etc..).
>
> If it's *really important* for you I can write it next week(s),
> ... of course, my time is limited :-)
Hey, if I wanted that, there'd be a consulting fee involved, hey?
Actually, I just changed the field to VARCHAR and provided a limited
range of options. Since there is not to_char('7 +00:00:00') yet, but
interval('1 month') works great, it makes more sense to store my data as
text.
Since I'm not writing the temporary interval2char function, I'll
mention that it seemed to me that it could be broken down into a series
of IF ... THEN statements either testing DATEPART or against other
INTERVAL values. A string could be built against the components of the
Interval.
Now, two follow-up questions:
1. Does ALTER TABLE in 7.1 beta 4 allow DROP COLUMN? I can't seem to
get it to work.
2. Has anyone given thought to a VB-style SELECT CASE (which we should
call 'SELECT MATCH') statement in PL/pgSQL? Different from the CASE
that allows you to select column values in the SELECT clause, SELECT
MATCH would be an IF ... THEN style structure offering an indefinite
numebr of options. I'm sure that PL/SQL has something like this ...
I'll look it up later today.
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete
informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small
businesses fax 621-2533 and non-profit organizations. San Francisco