Re: [PATCHES] ISO year.
Patch applied. Thanks.
---
Kurt Roeckx wrote:
> On Thu, Dec 18, 2003 at 06:47:41PM +0100, Peter Eisentraut wrote:
> > Kurt Roeckx wrote:
> > > I have no idea if this in Oracle or not. But it's something I
> > > needed, and other people in the past asked about it too.
> >
> > It is in Oracle, but you aren't exactly on the spot. It should be
> >
> > IYYY - 4 digits ('2003')
> > IYY - 3 digits ('003')
> > IY - 2 digits ('03')
> > I- 1 digit ('3')
>
> Here is an updated patch that does that.
>
>
> Kurt
>
[ Attachment, skipping... ]
>
> ---(end of broadcast)---
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] ISO year.
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
I will try to apply it within the next 48 hours.
---
Kurt Roeckx wrote:
> On Thu, Dec 18, 2003 at 06:47:41PM +0100, Peter Eisentraut wrote:
> > Kurt Roeckx wrote:
> > > I have no idea if this in Oracle or not. But it's something I
> > > needed, and other people in the past asked about it too.
> >
> > It is in Oracle, but you aren't exactly on the spot. It should be
> >
> > IYYY - 4 digits ('2003')
> > IYY - 3 digits ('003')
> > IY - 2 digits ('03')
> > I- 1 digit ('3')
>
> Here is an updated patch that does that.
>
>
> Kurt
>
[ Attachment, skipping... ]
>
> ---(end of broadcast)---
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [PATCHES] ISO year.
On Fri, Dec 19, 2003 at 07:14:53PM -0500, Bruce Momjian wrote: > > Which patch is OK? The one attached? You looked like you were making > changes to this patch in your later emails. That is the changed/good patch. Kurt ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] ISO year.
Karel Zak wrote:
> On Thu, Dec 18, 2003 at 08:18:08PM +0100, Kurt Roeckx wrote:
> > DCH_IW,
> > + DCH_IYYY,
> > + DCH_IYY,
> > + DCH_IY,
> > + DCH_I,
>
> It's better :-) This patch is OK for me.
Which patch is OK? The one attached? You looked like you were making
changes to this patch in your later emails.
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
Index: doc/src/sgml/func.sgml
===
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/func.sgml,v
retrieving revision 1.182
diff -u -r1.182 func.sgml
--- doc/src/sgml/func.sgml 16 Dec 2003 15:27:58 - 1.182
+++ doc/src/sgml/func.sgml 18 Dec 2003 18:57:56 -
@@ -3981,6 +3981,22 @@
last digit of year
+ IYYY
+ ISO year (4 and more digits)
+
+
+ IYY
+ last 3 digits of ISO year
+
+
+ IY
+ last 2 digits of ISO year
+
+
+ I
+ last digits of ISO year
+
+
BC or B.C. or
AD or A.D.
era indicator (upper case)
Index: src/include/utils/timestamp.h
===
RCS file: /projects/cvsroot/pgsql-server/src/include/utils/timestamp.h,v
retrieving revision 1.32
diff -u -r1.32 timestamp.h
--- src/include/utils/timestamp.h 29 Nov 2003 22:41:16 - 1.32
+++ src/include/utils/timestamp.h 18 Dec 2003 18:57:57 -
@@ -248,5 +248,6 @@
extern void isoweek2date(int woy, int *year, int *mon, int *mday);
extern int date2isoweek(int year, int mon, int mday);
+extern int date2isoyear(int year, int mon, int mday);
#endif /* TIMESTAMP_H */
Index: src/backend/utils/adt/formatting.c
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/adt/formatting.c,v
retrieving revision 1.70
diff -u -r1.70 formatting.c
--- src/backend/utils/adt/formatting.c 29 Nov 2003 19:51:58 - 1.70
+++ src/backend/utils/adt/formatting.c 18 Dec 2003 18:57:58 -
@@ -525,6 +525,10 @@
DCH_HH12,
DCH_HH,
DCH_IW,
+ DCH_IYYY,
+ DCH_IYY,
+ DCH_IY,
+ DCH_I,
DCH_J,
DCH_MI,
DCH_MM,
@@ -565,6 +569,10 @@
DCH_hh12,
DCH_hh,
DCH_iw,
+ DCH_iyyy,
+ DCH_iyy,
+ DCH_iy,
+ DCH_i,
DCH_j,
DCH_mi,
DCH_mm,
@@ -659,6 +667,10 @@
{"HH12", 4, dch_time, DCH_HH12, TRUE},
{"HH", 2, dch_time, DCH_HH, TRUE},
{"IW", 2, dch_date, DCH_IW, TRUE}, /* I */
+ {"IYYY", 4, dch_date, DCH_IYYY, TRUE},
+ {"IYY", 3, dch_date, DCH_IYY, TRUE},
+ {"IY", 2, dch_date, DCH_IY, TRUE},
+ {"I", 1, dch_date, DCH_I, TRUE},
{"J", 1, dch_date, DCH_J, TRUE},/* J */
{"MI", 2, dch_time, DCH_MI, TRUE},
{"MM", 2, dch_date, DCH_MM, TRUE},
@@ -699,6 +711,10 @@
{"hh12", 4, dch_time, DCH_HH12, TRUE},
{"hh", 2, dch_time, DCH_HH, TRUE},
{"iw", 2, dch_date, DCH_IW, TRUE}, /* i */
+ {"iyyy", 4, dch_date, DCH_IYYY, TRUE},
+ {"iyy", 3, dch_date, DCH_IYY, TRUE},
+ {"iy", 2, dch_date, DCH_IY, TRUE},
+ {"i", 1, dch_date, DCH_I, TRUE},
{"j", 1, dch_time, DCH_J, TRUE},/* j */
{"mi", 2, dch_time, DCH_MI, TRUE}, /* m */
{"mm", 2, dch_date, DCH_MM, TRUE},
@@ -2447,12 +2463,26 @@
}
break;
case DCH_:
+ case DCH_IYYY:
if (flag == TO_CHAR)
{
if (tm->tm_year <= && tm->tm_year >= -9998)
- sprintf(inout, "%0*d", S_FM(suf) ? 0 : 4,
YEAR_ABS(tm->tm_year));
- else
- sprintf(inout, "%d", YEAR_ABS(tm->tm_year));
+ sprintf(inout, "%0*d",
+ S_FM(suf) ? 0 : 4,
+ arg == DCH_ ?
+ YEAR_ABS(tm->tm_year) :
+ YEAR_ABS(date2isoyear(
+ tm->tm_year,
+ tm->tm_mon,
+ tm->tm_mday)));
+ else
+ sprintf(inout, "%d",
+ arg == DCH_ ?
+ YEAR_ABS(tm->tm_year) :
+
Re: [PATCHES] ISO year.
On Thu, Dec 18, 2003 at 08:18:08PM +0100, Kurt Roeckx wrote: > DCH_IW, > + DCH_IYYY, > + DCH_IYY, > + DCH_IY, > + DCH_I, It's better :-) This patch is OK for me. Karel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] ISO year.
On Thu, Dec 18, 2003 at 11:41:18AM -0500, Tom Lane wrote: > Kurt Roeckx <[EMAIL PROTECTED]> writes: > > This patch allows you to use I as format specifier to get the > > ISO year, the year correspondeing to the ISO week number (IW). > > The purpose of to_char() as I understand it is to be 100% Oracle > compatible, not to invent new features at random. Is this duplicating > some Oracle functionality that was left out? Oracle has I too. So thanks to Kurt, because now we approximate to 100% compatibity :-) BTW, I'm open for to_char() PostgreSQL extensions if it will good described in docs and marked as PostgreSQL extension. Karel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] ISO year.
On Thu, Dec 18, 2003 at 04:33:44PM +0100, Kurt Roeckx wrote: > + DCH_I, ^^^ > DCH_, > DCH_YYY, > DCH_YY, There is a bug.. it's wrong position. Please, add the "enum" of DCH_I to same position as you have "I" in next array. It means "enum" DCH_I must be behind DCH_IW. If you will add others IY the order must be: DCH_IW DCH_I DCH_IYYY DCH_IYY DCH_IY .. and same for lower case version: > @@ -582,6 +583,7 @@ > DCH_ww, > DCH_w, > DCH_y_yyy, > + DCH_i, ^ Thanks! Karel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] ISO year.
On Thu, Dec 18, 2003 at 06:47:41PM +0100, Peter Eisentraut wrote:
> Kurt Roeckx wrote:
> > I have no idea if this in Oracle or not. But it's something I
> > needed, and other people in the past asked about it too.
>
> It is in Oracle, but you aren't exactly on the spot. It should be
>
> IYYY - 4 digits ('2003')
> IYY - 3 digits ('003')
> IY - 2 digits ('03')
> I- 1 digit ('3')
Here is an updated patch that does that.
Kurt
Index: doc/src/sgml/func.sgml
===
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/func.sgml,v
retrieving revision 1.182
diff -u -r1.182 func.sgml
--- doc/src/sgml/func.sgml 16 Dec 2003 15:27:58 - 1.182
+++ doc/src/sgml/func.sgml 18 Dec 2003 18:57:56 -
@@ -3981,6 +3981,22 @@
last digit of year
+ IYYY
+ ISO year (4 and more digits)
+
+
+ IYY
+ last 3 digits of ISO year
+
+
+ IY
+ last 2 digits of ISO year
+
+
+ I
+ last digits of ISO year
+
+
BC or B.C. or
AD or A.D.
era indicator (upper case)
Index: src/include/utils/timestamp.h
===
RCS file: /projects/cvsroot/pgsql-server/src/include/utils/timestamp.h,v
retrieving revision 1.32
diff -u -r1.32 timestamp.h
--- src/include/utils/timestamp.h 29 Nov 2003 22:41:16 - 1.32
+++ src/include/utils/timestamp.h 18 Dec 2003 18:57:57 -
@@ -248,5 +248,6 @@
extern void isoweek2date(int woy, int *year, int *mon, int *mday);
extern int date2isoweek(int year, int mon, int mday);
+extern int date2isoyear(int year, int mon, int mday);
#endif /* TIMESTAMP_H */
Index: src/backend/utils/adt/formatting.c
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/adt/formatting.c,v
retrieving revision 1.70
diff -u -r1.70 formatting.c
--- src/backend/utils/adt/formatting.c 29 Nov 2003 19:51:58 - 1.70
+++ src/backend/utils/adt/formatting.c 18 Dec 2003 18:57:58 -
@@ -525,6 +525,10 @@
DCH_HH12,
DCH_HH,
DCH_IW,
+ DCH_IYYY,
+ DCH_IYY,
+ DCH_IY,
+ DCH_I,
DCH_J,
DCH_MI,
DCH_MM,
@@ -565,6 +569,10 @@
DCH_hh12,
DCH_hh,
DCH_iw,
+ DCH_iyyy,
+ DCH_iyy,
+ DCH_iy,
+ DCH_i,
DCH_j,
DCH_mi,
DCH_mm,
@@ -659,6 +667,10 @@
{"HH12", 4, dch_time, DCH_HH12, TRUE},
{"HH", 2, dch_time, DCH_HH, TRUE},
{"IW", 2, dch_date, DCH_IW, TRUE}, /* I */
+ {"IYYY", 4, dch_date, DCH_IYYY, TRUE},
+ {"IYY", 3, dch_date, DCH_IYY, TRUE},
+ {"IY", 2, dch_date, DCH_IY, TRUE},
+ {"I", 1, dch_date, DCH_I, TRUE},
{"J", 1, dch_date, DCH_J, TRUE},/* J */
{"MI", 2, dch_time, DCH_MI, TRUE},
{"MM", 2, dch_date, DCH_MM, TRUE},
@@ -699,6 +711,10 @@
{"hh12", 4, dch_time, DCH_HH12, TRUE},
{"hh", 2, dch_time, DCH_HH, TRUE},
{"iw", 2, dch_date, DCH_IW, TRUE}, /* i */
+ {"iyyy", 4, dch_date, DCH_IYYY, TRUE},
+ {"iyy", 3, dch_date, DCH_IYY, TRUE},
+ {"iy", 2, dch_date, DCH_IY, TRUE},
+ {"i", 1, dch_date, DCH_I, TRUE},
{"j", 1, dch_time, DCH_J, TRUE},/* j */
{"mi", 2, dch_time, DCH_MI, TRUE}, /* m */
{"mm", 2, dch_date, DCH_MM, TRUE},
@@ -2447,12 +2463,26 @@
}
break;
case DCH_:
+ case DCH_IYYY:
if (flag == TO_CHAR)
{
if (tm->tm_year <= && tm->tm_year >= -9998)
- sprintf(inout, "%0*d", S_FM(suf) ? 0 : 4,
YEAR_ABS(tm->tm_year));
- else
- sprintf(inout, "%d", YEAR_ABS(tm->tm_year));
+ sprintf(inout, "%0*d",
+ S_FM(suf) ? 0 : 4,
+ arg == DCH_ ?
+ YEAR_ABS(tm->tm_year) :
+ YEAR_ABS(date2isoyear(
+ tm->tm_year,
+ tm->tm_mon,
+ tm->tm_mday)));
+ else
+ sprintf(inout, "%d",
+ arg == DCH_ ?
+ YEAR_ABS(tm->tm_year) :
+ YEAR_ABS(date2isoyear(
+ tm->tm_year,
+
Re: [PATCHES] ISO year.
On Thu, Dec 18, 2003 at 06:47:41PM +0100, Peter Eisentraut wrote:
> Kurt Roeckx wrote:
> > I have no idea if this in Oracle or not. But it's something I
> > needed, and other people in the past asked about it too.
>
> It is in Oracle, but you aren't exactly on the spot. It should be
>
> IYYY - 4 digits ('2003')
> IYY - 3 digits ('003')
> IY - 2 digits ('03')
> I- 1 digit ('3')
I'll update the patch to support all of them.
Kurt
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] ISO year.
Kurt Roeckx wrote:
> I have no idea if this in Oracle or not. But it's something I
> needed, and other people in the past asked about it too.
It is in Oracle, but you aren't exactly on the spot. It should be
IYYY - 4 digits ('2003')
IYY - 3 digits ('003')
IY - 2 digits ('03')
I- 1 digit ('3')
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] ISO year.
On Thu, Dec 18, 2003 at 11:41:18AM -0500, Tom Lane wrote: > Kurt Roeckx <[EMAIL PROTECTED]> writes: > > This patch allows you to use I as format specifier to get the > > ISO year, the year correspondeing to the ISO week number (IW). > > The purpose of to_char() as I understand it is to be 100% Oracle > compatible, not to invent new features at random. Is this duplicating > some Oracle functionality that was left out? I have no idea if this in Oracle or not. But it's something I needed, and other people in the past asked about it too. You could use the normal year instead of the iso year, but around newyear this always gives problems. Kurt ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] ISO year.
Kurt Roeckx <[EMAIL PROTECTED]> writes: > This patch allows you to use I as format specifier to get the > ISO year, the year correspondeing to the ISO week number (IW). The purpose of to_char() as I understand it is to be 100% Oracle compatible, not to invent new features at random. Is this duplicating some Oracle functionality that was left out? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[PATCHES] ISO year.
This patch allows you to use I as format specifier to get the
ISO year, the year correspondeing to the ISO week number (IW).
Kurt
Index: doc/src/sgml/func.sgml
===
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/func.sgml,v
retrieving revision 1.182
diff -u -r1.182 func.sgml
--- doc/src/sgml/func.sgml 16 Dec 2003 15:27:58 - 1.182
+++ doc/src/sgml/func.sgml 18 Dec 2003 15:29:28 -
@@ -3981,6 +3981,10 @@
last digit of year
+ I
+ ISO year (The first Thursday of the new year is in week 1.)
+
+
BC or B.C. or
AD or A.D.
era indicator (upper case)
Index: src/include/utils/timestamp.h
===
RCS file: /projects/cvsroot/pgsql-server/src/include/utils/timestamp.h,v
retrieving revision 1.32
diff -u -r1.32 timestamp.h
--- src/include/utils/timestamp.h 29 Nov 2003 22:41:16 - 1.32
+++ src/include/utils/timestamp.h 18 Dec 2003 15:29:28 -
@@ -248,5 +248,6 @@
extern void isoweek2date(int woy, int *year, int *mon, int *mday);
extern int date2isoweek(int year, int mon, int mday);
+extern int date2isoyear(int year, int mon, int mday);
#endif /* TIMESTAMP_H */
Index: src/backend/utils/adt/formatting.c
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/adt/formatting.c,v
retrieving revision 1.70
diff -u -r1.70 formatting.c
--- src/backend/utils/adt/formatting.c 29 Nov 2003 19:51:58 - 1.70
+++ src/backend/utils/adt/formatting.c 18 Dec 2003 15:29:30 -
@@ -544,6 +544,7 @@
DCH_WW,
DCH_W,
DCH_Y_YYY,
+ DCH_I,
DCH_,
DCH_YYY,
DCH_YY,
@@ -582,6 +583,7 @@
DCH_ww,
DCH_w,
DCH_y_yyy,
+ DCH_i,
DCH_,
DCH_yyy,
DCH_yy,
@@ -659,6 +661,7 @@
{"HH12", 4, dch_time, DCH_HH12, TRUE},
{"HH", 2, dch_time, DCH_HH, TRUE},
{"IW", 2, dch_date, DCH_IW, TRUE}, /* I */
+ {"I", 5, dch_date, DCH_I, TRUE},
{"J", 1, dch_date, DCH_J, TRUE},/* J */
{"MI", 2, dch_time, DCH_MI, TRUE},
{"MM", 2, dch_date, DCH_MM, TRUE},
@@ -699,6 +702,7 @@
{"hh12", 4, dch_time, DCH_HH12, TRUE},
{"hh", 2, dch_time, DCH_HH, TRUE},
{"iw", 2, dch_date, DCH_IW, TRUE}, /* i */
+ {"i", 5, dch_date, DCH_I, TRUE},
{"j", 1, dch_time, DCH_J, TRUE},/* j */
{"mi", 2, dch_time, DCH_MI, TRUE}, /* m */
{"mm", 2, dch_date, DCH_MM, TRUE},
@@ -2444,6 +2448,41 @@
tmfc->year += (cc * 1000);
return strdigits_len(inout) + 3 + SKIP_THth(suf);
+ }
+ break;
+ case DCH_I:
+ if (flag == TO_CHAR)
+ {
+ if (tm->tm_year <= && tm->tm_year >= -9998)
+ sprintf(inout, "%0*d",
+ S_FM(suf) ? 0 : 4,
+ YEAR_ABS(date2isoyear(
+ tm->tm_year,
+ tm->tm_mon,
+ tm->tm_mday)));
+ else
+ sprintf(inout, "%d",
+ YEAR_ABS(date2isoyear(
+ tm->tm_year,
+ tm->tm_mon,
+ tm->tm_mday)));
+
+ if (S_THth(suf))
+ str_numth(p_inout, inout, S_TH_TYPE(suf));
+ return strlen(p_inout) - 1;
+ }
+ else if (flag == FROM_CHAR)
+ {
+ if (S_FM(suf) || is_next_separator(node))
+ {
+ sscanf(inout, "%d", &tmfc->year);
+ return strdigits_len(inout) - 1 +
SKIP_THth(suf);
+ }
+ else
+ {
+ sscanf(inout, "%04d", &tmfc->year);
+ return 3 + SKIP_THth(suf);
+ }
}
break;
case DCH_:
Index: src/backend/utils/adt/timestamp.c
===
RCS file: /projects/cvs
