Dates BC.

Started by Kurt Roeckxabout 22 years ago10 messages

Kurt Roeckx

Q@ping.be

about 22 years ago

I find this a little strange:

select date_part('year', '0002-01-01 BC'::date);
date_part
-----------
-1

It seems 1 BC and 0 are the same year.

In backend/utils/adt/formatting.c:

if (tmfc.bc)
{
if (tm->tm_year > 0)
tm->tm_year = -(tm->tm_year - 1);

It this normal or a bug?

Kurt

Bruce Momjian

pgman@candle.pha.pa.us

about 22 years ago

In reply to: Kurt Roeckx (#1)

Re: Dates BC.

Kurt Roeckx wrote:

I find this a little strange:

select date_part('year', '0002-01-01 BC'::date);
date_part
-----------
-1

It seems 1 BC and 0 are the same year.

In backend/utils/adt/formatting.c:

if (tmfc.bc)
{
if (tm->tm_year > 0)
tm->tm_year = -(tm->tm_year - 1);

It this normal or a bug?

Uh, well, yea, there was no year 0. However, it seems we should return
the proper year. My guess is that missing year 0 is the cause, and
there are certain reasons year 2 BC should return -1. If you are
subtracting dates, like 32AD - 4BC, you get 35, which is the proper
number of years spanned.

I am not sure what is the proper answer. I thought date_part just
grabbed "parts of the date" like it says, but obviously not, and there
are some good reasons for it, I guess.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Noname

david@fetter.org

about 22 years ago

In reply to: Kurt Roeckx (#1)

Re: Dates BC.

In article <20031218131120.GA11684@ping.be> you wrote:

I find this a little strange:

select date_part('year', '0002-01-01 BC'::date);
date_part
-----------
-1

It seems 1 BC and 0 are the same year.

There is an unresolveable legacy problem here, in that Brahmagupta did
not yet invent the mathematical concept of 0 until ~ 598 CE, by which
time the Roman Empire had fallen (depending on whether you believe it
actually fell). We'll just have to live with some weirdness on this
one. :)

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 cell: +1 415 235 3778

This is my .sig. There are many like it, but this one is mine.

Karel Zak

zakkr@zf.jcu.cz

about 22 years ago

In reply to: Kurt Roeckx (#1)

Re: Dates BC.

On Thu, Dec 18, 2003 at 02:11:20PM +0100, Kurt Roeckx wrote:

I find this a little strange:

select date_part('year', '0002-01-01 BC'::date);
date_part
-----------
-1

It seems 1 BC and 0 are the same year.

Is there connection between formatting.c and date_part() ?
I don't think so...

In backend/utils/adt/formatting.c:

if (tmfc.bc)
{
if (tm->tm_year > 0)
tm->tm_year = -(tm->tm_year - 1);

It this normal or a bug?

I think this code is OK, butg is somethere in extract (date_part) code.

test=# select to_date('0020-01-10 BC'::text, 'YYYY-MM-DD BC');
to_date
---------------
0020-01-10 BC
(1 ďż˝ďż˝dka)

test=# select to_date('0020-01-10 AD'::text, 'YYYY-MM-DD BC');
to_date
------------
0020-01-10

test=# select to_char('0020-01-10 BC'::date, 'YYYY-MM-DD AD');
to_char
---------------
0020-01-10 BC

Karel
--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/

Dann Corbit

DCorbit@connx.com

about 22 years ago

In reply to: Karel Zak (#4)

Re: Dates BC.

There is no zero calendar year. The first year of Anno Domini is 1. It's ordinal, not cardinal.

-----Original Message-----
From: Karel Zak [mailto:zakkr@zf.jcu.cz]
Sent: Friday, December 19, 2003 12:04 AM
To: Kurt Roeckx
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Dates BC.

On Thu, Dec 18, 2003 at 02:11:20PM +0100, Kurt Roeckx wrote:

I find this a little strange:

select date_part('year', '0002-01-01 BC'::date);
date_part
-----------
-1

It seems 1 BC and 0 are the same year.

Is there connection between formatting.c and date_part() ?
I don't think so...

In backend/utils/adt/formatting.c:

if (tmfc.bc)
{
if (tm->tm_year > 0)
tm->tm_year = -(tm->tm_year - 1);

It this normal or a bug?

I think this code is OK, butg is somethere in extract
(date_part) code.

test=# select to_date('0020-01-10 BC'::text, 'YYYY-MM-DD BC');
to_date
---------------
0020-01-10 BC
(1 řádka)

test=# select to_date('0020-01-10 AD'::text, 'YYYY-MM-DD BC');
to_date
------------
0020-01-10

test=# select to_char('0020-01-10 BC'::date, 'YYYY-MM-DD AD');
to_char
---------------
0020-01-10 BC

Karel
--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/

---------------------------(end of
broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Import Notes

Resolved by subject fallback

Karel Zak

zakkr@zf.jcu.cz

about 22 years ago

In reply to: Dann Corbit (#5)

Re: Dates BC.

On Fri, Dec 19, 2003 at 01:12:08AM -0800, Dann Corbit wrote:

There is no zero calendar year. The first year of Anno Domini is 1. It's ordinal, not cardinal.

I agree. But the follow quoted code is not use in date_part() there
Kurt found bug. It's used in to_timestamp() _only_, and it works,
because tm2timestamp() and date2j() work with zero year.

Is there connection between formatting.c and date_part() ?
I don't think so...

In backend/utils/adt/formatting.c:

if (tmfc.bc)
{
if (tm->tm_year > 0)
tm->tm_year = -(tm->tm_year - 1);

... "tm->tm_year = -(tm->tm_year - 1)" is used for:

# select to_timestamp('0001/01/01 BC', 'YYYY/MM/DD AD');
to_timestamp
------------------------
0001-01-01 00:00:00 BC

and it's OK.

I think a bug is somewhere in timestamp2tm() which used in next
examples and it's shared between more functions:

# select to_char('0001-01-01 BC'::date, 'YYYY/MM/DD AD');
to_char
---------------
0000/01/01 AD

# SELECT EXTRACT(YEAR from '0001-01-01 BC'::date);
date_part
-----------
0

Karel

--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/

Bruce Momjian

pgman@candle.pha.pa.us

almost 22 years ago

In reply to: Karel Zak (#6)

1 attachment(s)

Re: [HACKERS] Dates BC.

Karel Zak wrote:

On Fri, Dec 19, 2003 at 01:12:08AM -0800, Dann Corbit wrote:

There is no zero calendar year. The first year of Anno Domini is 1. It's ordinal, not cardinal.

I agree. But the follow quoted code is not use in date_part() there
Kurt found bug. It's used in to_timestamp() _only_, and it works,
because tm2timestamp() and date2j() work with zero year.

Is there connection between formatting.c and date_part() ?
I don't think so...

In backend/utils/adt/formatting.c:

if (tmfc.bc)
{
if (tm->tm_year > 0)
tm->tm_year = -(tm->tm_year - 1);

... "tm->tm_year = -(tm->tm_year - 1)" is used for:

# select to_timestamp('0001/01/01 BC', 'YYYY/MM/DD AD');
to_timestamp
------------------------
0001-01-01 00:00:00 BC

and it's OK.

I think a bug is somewhere in timestamp2tm() which used in next
examples and it's shared between more functions:

# select to_char('0001-01-01 BC'::date, 'YYYY/MM/DD AD');
to_char
---------------
0000/01/01 AD

# SELECT EXTRACT(YEAR from '0001-01-01 BC'::date);
date_part
-----------
0

Very interesting. I am attaching a patch that fixes these cases.
There were two bugs in our code:

o date_part didn't handle BC years properly (must -1 year)
o formatting code tested for BC dates as only < 0, not <= 0

Look at this before and after test output. The attached patdch fixes
this. Regression tests pass.

---------------------------------------------------------------------------

BEFORE:

test=> SELECT EXTRACT(YEAR from '0002-01-01 AD'::date);
date_part
-----------
2
(1 row)

test=> SELECT EXTRACT(YEAR from '0001-01-01 AD'::date);
date_part
-----------
1
(1 row)

test=> SELECT EXTRACT(YEAR from '0001-01-01 BC'::date);
date_part
-----------
0 **error**
(1 row)

test=> SELECT EXTRACT(YEAR from '0002-01-01 BC'::date);
date_part
-----------
-1 **error**
(1 row)

test=> select to_char('0002-01-01 AD'::date, 'YYYY/MM/DD AD');
to_char
---------------
0002/01/01 AD
(1 row)

test=> select to_char('0001-01-01 AD'::date, 'YYYY/MM/DD AD');
to_char
---------------
0001/01/01 AD
(1 row)

test=> select to_char('0001-01-01 BC'::date, 'YYYY/MM/DD AD');
to_char
---------------
0000/01/01 AD **error in year and AD**
(1 row)

test=> select to_char('0002-01-01 BC'::date, 'YYYY/MM/DD AD');
to_char
---------------
0002/01/01 BC
(1 row)

---------------------------------------------------------------------------

AFTER:

test=> SELECT EXTRACT(YEAR from '0002-01-01 AD'::date);
date_part
-----------
2
(1 row)

test=> SELECT EXTRACT(YEAR from '0001-01-01 AD'::date);
date_part
-----------
1
(1 row)

test=> SELECT EXTRACT(YEAR from '0001-01-01 BC'::date);
date_part
-----------
-1
(1 row)

test=> SELECT EXTRACT(YEAR from '0002-01-01 BC'::date);
date_part
-----------
-2
(1 row)

test=> select to_char('0002-01-01 AD'::date, 'YYYY/MM/DD AD');
to_char
---------------
0002/01/01 AD
(1 row)

test=> select to_char('0001-01-01 AD'::date, 'YYYY/MM/DD AD');
to_char
---------------
0001/01/01 AD
(1 row)

test=> select to_char('0001-01-01 BC'::date, 'YYYY/MM/DD AD');
to_char
---------------
0001/01/01 BC
(1 row)

test=> select to_char('0002-01-01 BC'::date, 'YYYY/MM/DD AD');
to_char
---------------
0002/01/01 BC
(1 row)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Attachments:

/pgpatches/bctext/plainDownload

Index: src/backend/utils/adt/formatting.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/utils/adt/formatting.c,v
retrieving revision 1.72
diff -c -c -r1.72 formatting.c
*** src/backend/utils/adt/formatting.c	7 Jan 2004 18:56:28 -0000	1.72
--- src/backend/utils/adt/formatting.c	29 Mar 2004 18:16:26 -0000
***************
*** 169,175 ****
   * AC / DC
   * ----------
   */
! #define YEAR_ABS(_y)	(_y < 0 ? -(_y -1) : _y)
  #define BC_STR_ORIG " BC"
  
  #define A_D_STR		"A.D."
--- 169,175 ----
   * AC / DC
   * ----------
   */
! #define YEAR_ABS(_y)	(_y <= 0 ? -(_y -1) : _y)
  #define BC_STR_ORIG " BC"
  
  #define A_D_STR		"A.D."
***************
*** 2119,2125 ****
  		case DCH_B_C:
  			if (flag == TO_CHAR)
  			{
! 				strcpy(inout, (tm->tm_year < 0 ? B_C_STR : A_D_STR));
  				return 3;
  
  			}
--- 2119,2125 ----
  		case DCH_B_C:
  			if (flag == TO_CHAR)
  			{
! 				strcpy(inout, (tm->tm_year <= 0 ? B_C_STR : A_D_STR));
  				return 3;
  
  			}
***************
*** 2134,2140 ****
  		case DCH_BC:
  			if (flag == TO_CHAR)
  			{
! 				strcpy(inout, (tm->tm_year < 0 ? BC_STR : AD_STR));
  				return 1;
  
  			}
--- 2134,2140 ----
  		case DCH_BC:
  			if (flag == TO_CHAR)
  			{
! 				strcpy(inout, (tm->tm_year <= 0 ? BC_STR : AD_STR));
  				return 1;
  
  			}
***************
*** 2149,2155 ****
  		case DCH_b_c:
  			if (flag == TO_CHAR)
  			{
! 				strcpy(inout, (tm->tm_year < 0 ? b_c_STR : a_d_STR));
  				return 3;
  
  			}
--- 2149,2155 ----
  		case DCH_b_c:
  			if (flag == TO_CHAR)
  			{
! 				strcpy(inout, (tm->tm_year <= 0 ? b_c_STR : a_d_STR));
  				return 3;
  
  			}
***************
*** 2164,2170 ****
  		case DCH_bc:
  			if (flag == TO_CHAR)
  			{
! 				strcpy(inout, (tm->tm_year < 0 ? bc_STR : ad_STR));
  				return 1;
  
  			}
--- 2164,2170 ----
  		case DCH_bc:
  			if (flag == TO_CHAR)
  			{
! 				strcpy(inout, (tm->tm_year <= 0 ? bc_STR : ad_STR));
  				return 1;
  
  			}
Index: src/backend/utils/adt/timestamp.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/utils/adt/timestamp.c,v
retrieving revision 1.102
diff -c -c -r1.102 timestamp.c
*** src/backend/utils/adt/timestamp.c	22 Mar 2004 01:38:17 -0000	1.102
--- src/backend/utils/adt/timestamp.c	29 Mar 2004 18:16:28 -0000
***************
*** 3261,3267 ****
  				break;
  
  			case DTK_YEAR:
! 				result = tm->tm_year;
  				break;
  
  			case DTK_DECADE:
--- 3261,3271 ----
  				break;
  
  			case DTK_YEAR:
! 				if (tm->tm_year > 0)
! 					result = tm->tm_year;
! 				else
! 					/* there is no year 0, just 1 BC and 1 AD*/
! 					result = tm->tm_year - 1;	
  				break;
  
  			case DTK_DECADE:

Bruce Momjian

pgman@candle.pha.pa.us

almost 22 years ago

In reply to: Karel Zak (#6)

1 attachment(s)

Re: [HACKERS] Dates BC.

Karel Zak wrote:

On Fri, Dec 19, 2003 at 01:12:08AM -0800, Dann Corbit wrote:

There is no zero calendar year. The first year of Anno Domini is 1. It's ordinal, not cardinal.

I agree. But the follow quoted code is not use in date_part() there
Kurt found bug. It's used in to_timestamp() _only_, and it works,
because tm2timestamp() and date2j() work with zero year.

I have also add a doc mention to my patch that mentions that there is no
0 AD, and therefore subtraction of BC years from AD years must be done
with caution.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Attachments:

/pgpatches/bctext/plainDownload

Index: doc/src/sgml/func.sgml
===================================================================
RCS file: /cvsroot/pgsql-server/doc/src/sgml/func.sgml,v
retrieving revision 1.195
diff -c -c -r1.195 func.sgml
*** doc/src/sgml/func.sgml	19 Mar 2004 19:13:26 -0000	1.195
--- doc/src/sgml/func.sgml	29 Mar 2004 20:31:28 -0000
***************
*** 5216,5223 ****
        <term><literal>week</literal></term>
        <listitem>
         <para>
!         The number of
!         the week of the year that the day is in.  By definition
          (<acronym>ISO</acronym> 8601), the first week of a year
          contains January 4 of that year.  (The <acronym>ISO</acronym>-8601
          week starts on Monday.)  In other words, the first Thursday of
--- 5216,5222 ----
        <term><literal>week</literal></term>
        <listitem>
         <para>
!         The number of the week of the year that the day is in.  By definition
          (<acronym>ISO</acronym> 8601), the first week of a year
          contains January 4 of that year.  (The <acronym>ISO</acronym>-8601
          week starts on Monday.)  In other words, the first Thursday of
***************
*** 5235,5241 ****
        <term><literal>year</literal></term>
        <listitem>
         <para>
!         The year field
         </para>
  
  <screen>
--- 5234,5241 ----
        <term><literal>year</literal></term>
        <listitem>
         <para>
!         The year field.  Keep in mind there is no <literal>0 AD</>, so subtracting 
!         <literal>BC</> years from <literal>AD</> years should be done with care.
         </para>
  
  <screen>
Index: src/backend/utils/adt/datetime.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/utils/adt/datetime.c,v
retrieving revision 1.125
diff -c -c -r1.125 datetime.c
*** src/backend/utils/adt/datetime.c	25 Feb 2004 19:41:23 -0000	1.125
--- src/backend/utils/adt/datetime.c	29 Mar 2004 20:31:34 -0000
***************
*** 93,99 ****
  	{"acsst", DTZ, POS(42)},	/* Cent. Australia */
  	{"acst", DTZ, NEG(16)},		/* Atlantic/Porto Acre Summer Time */
  	{"act", TZ, NEG(20)},		/* Atlantic/Porto Acre Time */
! 	{DA_D, ADBC, AD},			/* "ad" for years >= 0 */
  	{"adt", DTZ, NEG(12)},		/* Atlantic Daylight Time */
  	{"aesst", DTZ, POS(44)},	/* E. Australia */
  	{"aest", TZ, POS(40)},		/* Australia Eastern Std Time */
--- 93,99 ----
  	{"acsst", DTZ, POS(42)},	/* Cent. Australia */
  	{"acst", DTZ, NEG(16)},		/* Atlantic/Porto Acre Summer Time */
  	{"act", TZ, NEG(20)},		/* Atlantic/Porto Acre Time */
! 	{DA_D, ADBC, AD},			/* "ad" for years > 0 */
  	{"adt", DTZ, NEG(12)},		/* Atlantic Daylight Time */
  	{"aesst", DTZ, POS(44)},	/* E. Australia */
  	{"aest", TZ, POS(40)},		/* Australia Eastern Std Time */
***************
*** 139,145 ****
  	{"azot", TZ, NEG(4)},		/* Azores Time */
  	{"azst", DTZ, POS(20)},		/* Azerbaijan Summer Time */
  	{"azt", TZ, POS(16)},		/* Azerbaijan Time */
! 	{DB_C, ADBC, BC},			/* "bc" for years < 0 */
  	{"bdst", TZ, POS(8)},		/* British Double Summer Time */
  	{"bdt", TZ, POS(24)},		/* Dacca */
  	{"bnt", TZ, POS(32)},		/* Brunei Darussalam Time */
--- 139,145 ----
  	{"azot", TZ, NEG(4)},		/* Azores Time */
  	{"azst", DTZ, POS(20)},		/* Azerbaijan Summer Time */
  	{"azt", TZ, POS(16)},		/* Azerbaijan Time */
! 	{DB_C, ADBC, BC},			/* "bc" for years <= 0 */
  	{"bdst", TZ, POS(8)},		/* British Double Summer Time */
  	{"bdt", TZ, POS(24)},		/* Dacca */
  	{"bnt", TZ, POS(32)},		/* Brunei Darussalam Time */
Index: src/backend/utils/adt/formatting.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/utils/adt/formatting.c,v
retrieving revision 1.72
diff -c -c -r1.72 formatting.c
*** src/backend/utils/adt/formatting.c	7 Jan 2004 18:56:28 -0000	1.72
--- src/backend/utils/adt/formatting.c	29 Mar 2004 20:31:40 -0000
***************
*** 169,175 ****
   * AC / DC
   * ----------
   */
! #define YEAR_ABS(_y)	(_y < 0 ? -(_y -1) : _y)
  #define BC_STR_ORIG " BC"
  
  #define A_D_STR		"A.D."
--- 169,175 ----
   * AC / DC
   * ----------
   */
! #define YEAR_ABS(_y)	(_y <= 0 ? -(_y -1) : _y)
  #define BC_STR_ORIG " BC"
  
  #define A_D_STR		"A.D."
***************
*** 2119,2125 ****
  		case DCH_B_C:
  			if (flag == TO_CHAR)
  			{
! 				strcpy(inout, (tm->tm_year < 0 ? B_C_STR : A_D_STR));
  				return 3;
  
  			}
--- 2119,2125 ----
  		case DCH_B_C:
  			if (flag == TO_CHAR)
  			{
! 				strcpy(inout, (tm->tm_year <= 0 ? B_C_STR : A_D_STR));
  				return 3;
  
  			}
***************
*** 2134,2140 ****
  		case DCH_BC:
  			if (flag == TO_CHAR)
  			{
! 				strcpy(inout, (tm->tm_year < 0 ? BC_STR : AD_STR));
  				return 1;
  
  			}
--- 2134,2140 ----
  		case DCH_BC:
  			if (flag == TO_CHAR)
  			{
! 				strcpy(inout, (tm->tm_year <= 0 ? BC_STR : AD_STR));
  				return 1;
  
  			}
***************
*** 2149,2155 ****
  		case DCH_b_c:
  			if (flag == TO_CHAR)
  			{
! 				strcpy(inout, (tm->tm_year < 0 ? b_c_STR : a_d_STR));
  				return 3;
  
  			}
--- 2149,2155 ----
  		case DCH_b_c:
  			if (flag == TO_CHAR)
  			{
! 				strcpy(inout, (tm->tm_year <= 0 ? b_c_STR : a_d_STR));
  				return 3;
  
  			}
***************
*** 2164,2170 ****
  		case DCH_bc:
  			if (flag == TO_CHAR)
  			{
! 				strcpy(inout, (tm->tm_year < 0 ? bc_STR : ad_STR));
  				return 1;
  
  			}
--- 2164,2170 ----
  		case DCH_bc:
  			if (flag == TO_CHAR)
  			{
! 				strcpy(inout, (tm->tm_year <= 0 ? bc_STR : ad_STR));
  				return 1;
  
  			}
Index: src/backend/utils/adt/timestamp.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/utils/adt/timestamp.c,v
retrieving revision 1.102
diff -c -c -r1.102 timestamp.c
*** src/backend/utils/adt/timestamp.c	22 Mar 2004 01:38:17 -0000	1.102
--- src/backend/utils/adt/timestamp.c	29 Mar 2004 20:31:44 -0000
***************
*** 3261,3267 ****
  				break;
  
  			case DTK_YEAR:
! 				result = tm->tm_year;
  				break;
  
  			case DTK_DECADE:
--- 3261,3271 ----
  				break;
  
  			case DTK_YEAR:
! 				if (tm->tm_year > 0)
! 					result = tm->tm_year;
! 				else
! 					/* there is no year 0, just 1 BC and 1 AD*/
! 					result = tm->tm_year - 1;	
  				break;
  
  			case DTK_DECADE:

Karel Zak

zakkr@zf.jcu.cz

almost 22 years ago

In reply to: Bruce Momjian (#8)

Re: [HACKERS] Dates BC.

On Mon, Mar 29, 2004 at 03:37:07PM -0500, Bruce Momjian wrote:

Karel Zak wrote:

On Fri, Dec 19, 2003 at 01:12:08AM -0800, Dann Corbit wrote:

There is no zero calendar year. The first year of Anno Domini is 1. It's ordinal, not cardinal.

I agree. But the follow quoted code is not use in date_part() there
Kurt found bug. It's used in to_timestamp() _only_, and it works,
because tm2timestamp() and date2j() work with zero year.

I have also add a doc mention to my patch that mentions that there is no
0 AD, and therefore subtraction of BC years from AD years must be done
with caution.

The patch seems good for me. Thanks.

Karel

--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/

#10

Bruce Momjian

pgman@candle.pha.pa.us

almost 22 years ago

In reply to: Karel Zak (#6)

Re: Dates BC.

I have applied a patch to fix the issues mentioned below. Thanks.

---------------------------------------------------------------------------

Karel Zak wrote:

On Fri, Dec 19, 2003 at 01:12:08AM -0800, Dann Corbit wrote:

There is no zero calendar year. The first year of Anno Domini is 1. It's ordinal, not cardinal.

I agree. But the follow quoted code is not use in date_part() there
Kurt found bug. It's used in to_timestamp() _only_, and it works,
because tm2timestamp() and date2j() work with zero year.

Is there connection between formatting.c and date_part() ?
I don't think so...

In backend/utils/adt/formatting.c:

if (tmfc.bc)
{
if (tm->tm_year > 0)
tm->tm_year = -(tm->tm_year - 1);

... "tm->tm_year = -(tm->tm_year - 1)" is used for:

# select to_timestamp('0001/01/01 BC', 'YYYY/MM/DD AD');
to_timestamp
------------------------
0001-01-01 00:00:00 BC

and it's OK.

I think a bug is somewhere in timestamp2tm() which used in next
examples and it's shared between more functions:

# select to_char('0001-01-01 BC'::date, 'YYYY/MM/DD AD');
to_char
---------------
0000/01/01 AD

# SELECT EXTRACT(YEAR from '0001-01-01 BC'::date);
date_part
-----------
0

Karel

--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073