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 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 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 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.
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
[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 @@
entrylast digit of year/entry
/row
row
+ entryliteralI/literal/entry
+ entryISO year (The first Thursday of the new year is in week 1.)/entry
+ /row
+ row
entryliteralBC/literal or literalB.C./literal or
literalAD/literal or literalA.D./literal/entry
entryera indicator (upper case)/entry
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
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 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 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.
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 @@
entrylast digit of year/entry
/row
row
+ entryliteralIYYY/literal/entry
+ entryISO year (4 and more digits)/entry
+ /row
+ row
+ entryliteralIYY/literal/entry
+ entrylast 3 digits of ISO year/entry
+ /row
+ row
+ entryliteralIY/literal/entry
+ entrylast 2 digits of ISO year/entry
+ /row
+ row
+ entryliteralI/literal/entry
+ entrylast digits of ISO year/entry
+ /row
+ row
entryliteralBC/literal or literalB.C./literal or
literalAD/literal or literalA.D./literal/entry
entryera indicator (upper case)/entry
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_ ?
+
