Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit3f19987

Browse files
committed
here is a patch for formatting.c (to_char/timestampt()), for 7.1
 it fixing Y,YY,YYY,YYYY conversion, the docs and regress tests update are included too. During the patch testing I found small bug in miscadmin.h inconvertstr() declaration. Here it's fixed too. Thanks Karel
1 parent91ba4cc commit3f19987

File tree

5 files changed

+242
-100
lines changed

5 files changed

+242
-100
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -962,6 +962,20 @@
962962
keyword (example: <literal>'"Hello Year: "YYYY'</literal>).
963963
</para>
964964
</listitem>
965+
966+
<listitem>
967+
<para>
968+
<literal>YYYY</literal> conversion from string to timestamp or
969+
date is limited if you use year great than 4-digits. You must
970+
use after <literal>YYYY</literal> some non-digit char or template
971+
else year is always interpreted as 4-digits. For example (with year
972+
20000):
973+
<literal> to_date('200001131', 'YYYYMMDD') <literal> will bad
974+
interpreded as 4-digits year, right is use after year non-digit
975+
separator <literal> to_date('20000-1131', 'YYYY-MMDD')<literal> or
976+
<literal> to_date('20000Nov31', 'YYYYMonDD')<literal>.
977+
</para>
978+
</listitem>
965979
</itemizedlist>
966980
</para>
967981

‎src/backend/utils/adt/formatting.c

Lines changed: 179 additions & 95 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11
/* -----------------------------------------------------------------------
22
* formatting.c
33
*
4-
* $Header: /cvsroot/pgsql/src/backend/utils/adt/formatting.c,v 1.23 2000/10/29 13:17:34 petere Exp $
4+
* $Header: /cvsroot/pgsql/src/backend/utils/adt/formatting.c,v 1.24 2000/11/25 05:00:29 momjian Exp $
55
*
66
*
77
* Portions Copyright (c) 1999-2000, PostgreSQL, Inc
@@ -127,6 +127,7 @@ typedef struct
127127
intlen,/* keyword length*/
128128
(*action) (),
129129
id;/* keyword id*/
130+
boolisdigit;/* is expected output/input digit */
130131
}KeyWord;
131132

132133
typedefstruct
@@ -344,14 +345,16 @@ static intNUMCounter = 0;
344345
* ----------
345346
*/
346347
typedefstruct {
347-
inthh,am,pm,mi,ss,ssss,d,dd,ddd,mm,yyyy,bc,iw,ww,w,cc,q,j;
348+
inthh,am,pm,mi,ss,ssss,d,dd,ddd,mm,yyyy,yyy,yy,y,
349+
bc,iw,ww,w,cc,q,j;
348350
}TmFromChar;
349351

350352
#defineZERO_tmfc(_X )\
351353
do { \
352354
(_X)->hh= (_X)->am= (_X)->pm= (_X)->mi= (_X)->ss= (_X)->ssss= \
353-
(_X)->d= (_X)->dd= (_X)->ddd= (_X)->mm= (_X)->yyyy= (_X)->bc= \
354-
(_X)->iw= (_X)->ww= (_X)->w= (_X)->cc= (_X)->q= (_X)->j= 0; \
355+
(_X)->d= (_X)->dd= (_X)->ddd= (_X)->mm= (_X)->yyyy= (_X)->yyy= \
356+
(_X)->yy= (_X)->y= (_X)->bc= (_X)->iw= (_X)->ww= (_X)->w= \
357+
(_X)->cc= (_X)->q= (_X)->j= 0; \
355358
} while(0)
356359

357360
#ifdefDEBUG_TO_FROM_CHAR
@@ -453,7 +456,7 @@ static KeySuffix DCH_suff[] = {
453456
* it is not good.
454457
*
455458
* (!)
456-
* Position for the keyword is simular as position in the enum DCH/NUM_poz
459+
*- Position for the keyword is simular as position in the enum DCH/NUM_poz.
457460
* (!)
458461
*
459462
* For fast search is used the 'int index[]', index is ascii table from position
@@ -598,88 +601,88 @@ typedef enum
598601
* ----------
599602
*/
600603
staticKeyWordDCH_keywords[]= {
601-
/*keyword,len,func.type is in Index */
602-
{"A.D.",4,dch_date,DCH_A_D},/* A */
603-
{"A.M.",4,dch_time,DCH_A_M},
604-
{"AD",2,dch_date,DCH_AD},
605-
{"AM",2,dch_time,DCH_AM},
606-
{"B.C.",4,dch_date,DCH_B_C},/* B */
607-
{"BC",2,dch_date,DCH_BC},
608-
{"CC",2,dch_date,DCH_CC},/* C */
609-
{"DAY",3,dch_date,DCH_DAY},/* D */
610-
{"DDD",3,dch_date,DCH_DDD},
611-
{"DD",2,dch_date,DCH_DD},
612-
{"DY",2,dch_date,DCH_DY},
613-
{"Day",3,dch_date,DCH_Day},
614-
{"Dy",2,dch_date,DCH_Dy},
615-
{"D",1,dch_date,DCH_D},
616-
{"FX",2,dch_global,DCH_FX},/* F */
617-
{"HH24",4,dch_time,DCH_HH24},/* H */
618-
{"HH12",4,dch_time,DCH_HH12},
619-
{"HH",2,dch_time,DCH_HH},
620-
{"IW",2,dch_date,DCH_IW},/* I */
621-
{"J",1,dch_date,DCH_J},/* J */
622-
{"MI",2,dch_time,DCH_MI},
623-
{"MM",2,dch_date,DCH_MM},
624-
{"MONTH",5,dch_date,DCH_MONTH},
625-
{"MON",3,dch_date,DCH_MON},
626-
{"Month",5,dch_date,DCH_Month},
627-
{"Mon",3,dch_date,DCH_Mon},
628-
{"P.M.",4,dch_time,DCH_P_M},/* P */
629-
{"PM",2,dch_time,DCH_PM},
630-
{"Q",1,dch_date,DCH_Q},/* Q */
631-
{"RM",2,dch_date,DCH_RM},/* R */
632-
{"SSSS",4,dch_time,DCH_SSSS},/* S */
633-
{"SS",2,dch_time,DCH_SS},
634-
{"TZ",2,dch_time,DCH_TZ},/* T */
635-
{"WW",2,dch_date,DCH_WW},/* W */
636-
{"W",1,dch_date,DCH_W},
637-
{"Y,YYY",5,dch_date,DCH_Y_YYY},/* Y */
638-
{"YYYY",4,dch_date,DCH_YYYY},
639-
{"YYY",3,dch_date,DCH_YYY},
640-
{"YY",2,dch_date,DCH_YY},
641-
{"Y",1,dch_date,DCH_Y},
642-
{"a.d.",4,dch_date,DCH_a_d},/* a */
643-
{"a.m.",4,dch_time,DCH_a_m},
644-
{"ad",2,dch_date,DCH_ad},
645-
{"am",2,dch_time,DCH_am},
646-
{"b.c.",4,dch_date,DCH_b_c},/* b */
647-
{"bc",2,dch_date,DCH_bc},
648-
{"cc",2,dch_date,DCH_CC},/* c */
649-
{"day",3,dch_date,DCH_day},/* d */
650-
{"ddd",3,dch_date,DCH_DDD},
651-
{"dd",2,dch_date,DCH_DD},
652-
{"dy",2,dch_date,DCH_dy},
653-
{"d",1,dch_date,DCH_D},
654-
{"fx",2,dch_global,DCH_FX},/* f */
655-
{"hh24",4,dch_time,DCH_HH24},/* h */
656-
{"hh12",4,dch_time,DCH_HH12},
657-
{"hh",2,dch_time,DCH_HH},
658-
{"iw",2,dch_date,DCH_IW},/* i */
659-
{"j",1,dch_time,DCH_J},/* j */
660-
{"mi",2,dch_time,DCH_MI},/* m */
661-
{"mm",2,dch_date,DCH_MM},
662-
{"month",5,dch_date,DCH_month},
663-
{"mon",3,dch_date,DCH_mon},
664-
{"p.m.",4,dch_time,DCH_p_m},/* p */
665-
{"pm",2,dch_time,DCH_pm},
666-
{"q",1,dch_date,DCH_Q},/* q */
667-
{"rm",2,dch_date,DCH_rm},/* r */
668-
{"ssss",4,dch_time,DCH_SSSS},/* s */
669-
{"ss",2,dch_time,DCH_SS},
670-
{"tz",2,dch_time,DCH_tz},/* t */
671-
{"ww",2,dch_date,DCH_WW},/* w */
672-
{"w",1,dch_date,DCH_W},
673-
{"y,yyy",5,dch_date,DCH_Y_YYY},/* y */
674-
{"yyyy",4,dch_date,DCH_YYYY},
675-
{"yyy",3,dch_date,DCH_YYY},
676-
{"yy",2,dch_date,DCH_YY},
677-
{"y",1,dch_date,DCH_Y},
604+
/*keyword,len,func,type, isdigit is in Index */
605+
{"A.D.",4,dch_date,DCH_A_D, FALSE},/* A */
606+
{"A.M.",4,dch_time,DCH_A_M, FALSE},
607+
{"AD",2,dch_date,DCH_AD, FALSE},
608+
{"AM",2,dch_time,DCH_AM, FALSE},
609+
{"B.C.",4,dch_date,DCH_B_C, FALSE},/* B */
610+
{"BC",2,dch_date,DCH_BC, FALSE},
611+
{"CC",2,dch_date,DCH_CC, TRUE},/* C */
612+
{"DAY",3,dch_date,DCH_DAY, FALSE},/* D */
613+
{"DDD",3,dch_date,DCH_DDD, TRUE},
614+
{"DD",2,dch_date,DCH_DD, TRUE},
615+
{"DY",2,dch_date,DCH_DY, FALSE},
616+
{"Day",3,dch_date,DCH_Day, FALSE},
617+
{"Dy",2,dch_date,DCH_Dy, FALSE},
618+
{"D",1,dch_date,DCH_D, TRUE},
619+
{"FX",2,dch_global,DCH_FX, FALSE},/* F */
620+
{"HH24",4,dch_time,DCH_HH24, TRUE},/* H */
621+
{"HH12",4,dch_time,DCH_HH12, TRUE},
622+
{"HH",2,dch_time,DCH_HH, TRUE},
623+
{"IW",2,dch_date,DCH_IW, TRUE},/* I */
624+
{"J",1,dch_date,DCH_J, TRUE},/* J */
625+
{"MI",2,dch_time,DCH_MI, TRUE},
626+
{"MM",2,dch_date,DCH_MM, TRUE},
627+
{"MONTH",5,dch_date,DCH_MONTH, FALSE},
628+
{"MON",3,dch_date,DCH_MON, FALSE},
629+
{"Month",5,dch_date,DCH_Month, FALSE},
630+
{"Mon",3,dch_date,DCH_Mon, FALSE},
631+
{"P.M.",4,dch_time,DCH_P_M, FALSE},/* P */
632+
{"PM",2,dch_time,DCH_PM, FALSE},
633+
{"Q",1,dch_date,DCH_Q, TRUE},/* Q */
634+
{"RM",2,dch_date,DCH_RM, FALSE},/* R */
635+
{"SSSS",4,dch_time,DCH_SSSS, TRUE},/* S */
636+
{"SS",2,dch_time,DCH_SS, TRUE},
637+
{"TZ",2,dch_time,DCH_TZ, FALSE},/* T */
638+
{"WW",2,dch_date,DCH_WW, TRUE},/* W */
639+
{"W",1,dch_date,DCH_W, TRUE},
640+
{"Y,YYY",5,dch_date,DCH_Y_YYY, TRUE},/* Y */
641+
{"YYYY",4,dch_date,DCH_YYYY, TRUE},
642+
{"YYY",3,dch_date,DCH_YYY, TRUE},
643+
{"YY",2,dch_date,DCH_YY, TRUE},
644+
{"Y",1,dch_date,DCH_Y, TRUE},
645+
{"a.d.",4,dch_date,DCH_a_d, FALSE},/* a */
646+
{"a.m.",4,dch_time,DCH_a_m, FALSE},
647+
{"ad",2,dch_date,DCH_ad, FALSE},
648+
{"am",2,dch_time,DCH_am, FALSE},
649+
{"b.c.",4,dch_date,DCH_b_c, FALSE},/* b */
650+
{"bc",2,dch_date,DCH_bc, FALSE},
651+
{"cc",2,dch_date,DCH_CC, TRUE},/* c */
652+
{"day",3,dch_date,DCH_day, FALSE},/* d */
653+
{"ddd",3,dch_date,DCH_DDD, TRUE},
654+
{"dd",2,dch_date,DCH_DD, TRUE},
655+
{"dy",2,dch_date,DCH_dy, FALSE},
656+
{"d",1,dch_date,DCH_D, TRUE},
657+
{"fx",2,dch_global,DCH_FX, FALSE},/* f */
658+
{"hh24",4,dch_time,DCH_HH24, TRUE},/* h */
659+
{"hh12",4,dch_time,DCH_HH12, TRUE},
660+
{"hh",2,dch_time,DCH_HH, TRUE},
661+
{"iw",2,dch_date,DCH_IW, TRUE},/* i */
662+
{"j",1,dch_time,DCH_J, TRUE},/* j */
663+
{"mi",2,dch_time,DCH_MI, TRUE},/* m */
664+
{"mm",2,dch_date,DCH_MM, TRUE},
665+
{"month",5,dch_date,DCH_month, FALSE},
666+
{"mon",3,dch_date,DCH_mon, FALSE},
667+
{"p.m.",4,dch_time,DCH_p_m, FALSE},/* p */
668+
{"pm",2,dch_time,DCH_pm, FALSE},
669+
{"q",1,dch_date,DCH_Q, TRUE},/* q */
670+
{"rm",2,dch_date,DCH_rm, FALSE},/* r */
671+
{"ssss",4,dch_time,DCH_SSSS, TRUE},/* s */
672+
{"ss",2,dch_time,DCH_SS, TRUE},
673+
{"tz",2,dch_time,DCH_tz, FALSE},/* t */
674+
{"ww",2,dch_date,DCH_WW, TRUE},/* w */
675+
{"w",1,dch_date,DCH_W, TRUE},
676+
{"y,yyy",5,dch_date,DCH_Y_YYY, TRUE},/* y */
677+
{"yyyy",4,dch_date,DCH_YYYY, TRUE},
678+
{"yyy",3,dch_date,DCH_YYY, TRUE},
679+
{"yy",2,dch_date,DCH_YY, TRUE},
680+
{"y",1,dch_date,DCH_Y, TRUE},
678681
/* last */
679682
{NULL,0,NULL,0}};
680683

681684
/* ----------
682-
* KeyWords for NUMBER version
685+
* KeyWords for NUMBER version (now, isdigit info is not needful here..)
683686
* ----------
684687
*/
685688
staticKeyWordNUM_keywords[]= {
@@ -1230,7 +1233,7 @@ DCH_processor(FormatNode *node, char *inout, int flag)
12301233
* Skip blank space in FROM_CHAR's input
12311234
* ----------
12321235
*/
1233-
if (isspace(n->character)&&IS_FX==0)
1236+
if (isspace(n->character)&&IS_FX==0)
12341237
{
12351238
while (*s!='\0'&&isspace((int)*(s+1)))
12361239
++s;
@@ -1526,6 +1529,40 @@ dch_global(int arg, char *inout, int suf, int flag, FormatNode *node)
15261529
return-1;
15271530
}
15281531

1532+
/* ----------
1533+
* Return TRUE if next format picture is not digit value
1534+
* ----------
1535+
*/
1536+
staticbool
1537+
is_next_separator(FormatNode*n)
1538+
{
1539+
if (n->type==NODE_TYPE_END)
1540+
return FALSE;
1541+
1542+
if (n->type==NODE_TYPE_ACTION&&S_THth(n->suffix))
1543+
return TRUE;
1544+
1545+
/*
1546+
* Next node
1547+
*/
1548+
n++;
1549+
1550+
if (n->type==NODE_TYPE_END)
1551+
return FALSE;
1552+
1553+
if (n->type==NODE_TYPE_ACTION)
1554+
{
1555+
if (n->key->isdigit)
1556+
return FALSE;
1557+
1558+
return TRUE;
1559+
}
1560+
elseif (isdigit(n->character))
1561+
return FALSE;
1562+
1563+
return TRUE;/* some non-digit input (separator) */
1564+
}
1565+
15291566
#defineAMPM_ERRORelog(ERROR, "to_timestamp(): bad AM/PM string")
15301567

15311568
/* ----------
@@ -1736,7 +1773,10 @@ dch_time(int arg, char *inout, int suf, int flag, FormatNode *node)
17361773
}
17371774
elseif (flag==FROM_CHAR)
17381775
{
1739-
sscanf(inout,"%d",&tmfc->ssss);
1776+
if (is_next_separator(node))
1777+
sscanf(inout,"%d",&tmfc->ssss);
1778+
else
1779+
sscanf(inout,"%05d",&tmfc->ssss);
17401780
returnint4len((int4)tmfc->ssss)-1+SKIP_THth(suf);
17411781
}
17421782
break;
@@ -2192,7 +2232,11 @@ dch_date(int arg, char *inout, int suf, int flag, FormatNode *node)
21922232
}
21932233
elseif (flag==FROM_CHAR)
21942234
{
2195-
sscanf(inout,"%d",&tmfc->yyyy);
2235+
if (is_next_separator(node))
2236+
sscanf(inout,"%d",&tmfc->yyyy);
2237+
else
2238+
sscanf(inout,"%04d",&tmfc->yyyy);
2239+
21962240
if (!S_FM(suf)&&tmfc->yyyy <=9999&&tmfc->yyyy >=-9999)
21972241
len=4;
21982242
else
@@ -2217,7 +2261,7 @@ dch_date(int arg, char *inout, int suf, int flag, FormatNode *node)
22172261
}
22182262
elseif (flag==FROM_CHAR)
22192263
{
2220-
sscanf(inout,"%03d",&tmfc->yyyy);
2264+
sscanf(inout,"%03d",&tmfc->yyy);
22212265
return2+SKIP_THth(suf);
22222266
}
22232267
break;
@@ -2237,7 +2281,7 @@ dch_date(int arg, char *inout, int suf, int flag, FormatNode *node)
22372281
}
22382282
elseif (flag==FROM_CHAR)
22392283
{
2240-
sscanf(inout,"%02d",&tmfc->yyyy);
2284+
sscanf(inout,"%02d",&tmfc->yy);
22412285
return1+SKIP_THth(suf);
22422286
}
22432287
break;
@@ -2257,7 +2301,7 @@ dch_date(int arg, char *inout, int suf, int flag, FormatNode *node)
22572301
}
22582302
elseif (flag==FROM_CHAR)
22592303
{
2260-
sscanf(inout,"%1d",&tmfc->yyyy);
2304+
sscanf(inout,"%1d",&tmfc->y);
22612305
return0+SKIP_THth(suf);
22622306
}
22632307
break;
@@ -2725,15 +2769,55 @@ to_timestamp(PG_FUNCTION_ARGS)
27252769

27262770
if (tmfc->yyyy)
27272771
tm->tm_year=tmfc->yyyy;
2772+
2773+
elseif (tmfc->y)
2774+
{
2775+
/*
2776+
* 1-digit year:
2777+
*always +2000
2778+
*/
2779+
tm->tm_year=tmfc->y+2000;
2780+
}
2781+
elseif (tmfc->yy)
2782+
{
2783+
/*
2784+
* 2-digit year:
2785+
*'00' ... '69' = 2000 ... 2069
2786+
*'70' ... '99' = 1970 ... 1999
2787+
*/
2788+
tm->tm_year=tmfc->yy;
27282789

2729-
if (tmfc->j)
2730-
j2date(tmfc->j,&tm->tm_year,&tm->tm_mon,&tm->tm_mday);
2790+
if (tm->tm_year<70)
2791+
tm->tm_year+=2000;
2792+
else
2793+
tm->tm_year+=1900;
2794+
}
2795+
elseif (tmfc->yyy)
2796+
{
2797+
/*
2798+
* 3-digit year:
2799+
*'100' ... '999' = 1100 ... 1999
2800+
*'000' ... '099' = 2000 ... 2099
2801+
*/
2802+
tm->tm_year=tmfc->yyy;
27312803

2732-
if (tmfc->bc&&tm->tm_year>0)
2733-
tm->tm_year=-(tm->tm_year);
2804+
if (tm->tm_year >=100)
2805+
tm->tm_year+=1000;
2806+
else
2807+
tm->tm_year+=2000;
2808+
}
2809+
2810+
2811+
if (tmfc->bc)
2812+
{
2813+
if (tm->tm_year>0)
2814+
tm->tm_year=-(tm->tm_year-1);
2815+
else
2816+
elog(ERROR,"Inconsistant use of year %04d and 'BC'",tm->tm_year);
2817+
}
27342818

2735-
if (tm->tm_year<0)
2736-
tm->tm_year=tm->tm_year+1;
2819+
if (tmfc->j)
2820+
j2date(tmfc->j,&tm->tm_year,&tm->tm_mon,&tm->tm_mday);
27372821

27382822
if (tmfc->iw)
27392823
isoweek2date(tmfc->iw,&tm->tm_year,&tm->tm_mon,&tm->tm_mday);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp