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