date_part() BUG?
I�m making tests for data types and I wonder if the following is a bug!
using age() function I get: @ 1 hour 13 mins 27.88 secs
using date_part() over age() I get:
1 for 'hour' OK!
13 for 'minute' OK!
27.8765430000003 for 'second' OK!
0 for 'day' OK!
0 for 'month' OK!
0 for 'year' OK!
But
1 for 'decade' NOT OK!
1 for 'century' NOT OK!
1 for 'millenium' NOT OK!
See trancrition bellow
cd=> \d th
Table =
th
+----------------------------------+----------------------------------+--
-----+
| Field | Type
|
Length|
+----------------------------------+--------------------------------
--+-------+
| data | date
| 4 |
| hora | time
| 8 |
| ms | int4
| 4 |
| dt | datetime
| 8
|
+----------------------------------+----------------------------------+---
----+
cd=> select * from th
cd-> \g
data |hora | ms|dt----------+--------+----------+--------------------------
11/02/1999|10:33:
31|1234567890|11/02/1999 10:33:31.12
EDT
11/02/1999|10:33:31|1234567890|11/02/1999 10:33:31.13 EDT
(2
rows)
cd=> select age('now', dt) , date_part('millenium', age('now',
dt)::timespan), date_part('century', age('now', dt)::timespan),
date_part('decade', age('now', dt)::timespan) , date_part('year',
age('now', dt)::timespan), date_part('month', age('now', dt)::timespan),
date_part('day', age('now', dt)::timespan) , date_part('hour', age('now',
dt)::timespan) , date_part('minute', age('now', dt)::timespan) ,
date_part('second', age('now', dt)::timespan) from th\g
age
|date_part|date_part|date_part|date_part|date_part|date_part|date_part|date_
part|
date_part
---------------------------+---------+---------+---------+--------
-+---------+---------+---------+---------+----------------
@ 1 hour 13 mins
27.88 secs| 1| 1| 1| 0| 0| 0|
1| 13|27.8765430000003
@ 1 hour 13 mins 27.87 secs| 1|
1| 1| 0| 0| 0| 1|
13|27.8654319999996
(2 rows)
cd=>
------------------------------------------------------------------
Eng. Roberto Jo�o Lopes Garcia E-mail: roberto@mha.com.br
F. 55 11 848 9906 FAX 55 11 848 9955
MHA Engenharia Ltda
E-mail: mha@mha.com.br WWW: http://www.mha.com.br
Av Maria Coelho Aguiar, 215 Bloco D 2 Andar
Centro Empresarial de Sao Paulo
Sao Paulo - BRASIL - 05805 000
-------------------------------------------------------------------
I�m making tests for data types and I wonder if the following is a
bug!
using age() function I get: @ 1 hour 13 mins 27.88 secs
using date_part() over age() I get:
<snip tests for years through seconds>
But
1 for 'decade' NOT OK!
1 for 'century' NOT OK!
1 for 'millenium' NOT OK!
I can see that here. Will look at it. Thanks for the report...
- Tom
using age() function I get: @ 1 hour 13 mins 27.88 secs
using date_part() over age() I get:<snip tests for years through seconds>
But
1 for 'decade' NOT OK!
1 for 'century' NOT OK!
1 for 'millenium' NOT OK!I can see that here. Will look at it. Thanks for the report...
Sorry, it was a cut-and-paste error, with an explicit "+ 1" where it
shouldn't be. Patch enclosed, which includes another recent fix for date
input having mixed US/Euro-style formats and text months. If you've
already applied that one, then strip it out of the patch before applying
(or tell patch the right thing when it complains).
Thanks again for the report.
- Tom
Attachments:
dt.c.patchtext/plain; charset=us-ascii; name=dt.c.patchDownload
*** dt.c.orig Sat Jan 23 03:12:23 1999
--- dt.c Thu Feb 11 17:30:23 1999
***************
*** 249,255 ****
case DTK_DELTA:
if (tm2timespan(tm, fsec, span) != 0)
{
! #if FALSE
TIMESPAN_INVALID(span);
#endif
elog(ERROR, "Bad timespan external representation '%s'", str);
--- 249,255 ----
case DTK_DELTA:
if (tm2timespan(tm, fsec, span) != 0)
{
! #if NOT_USED
TIMESPAN_INVALID(span);
#endif
elog(ERROR, "Bad timespan external representation '%s'", str);
***************
*** 1412,1418 ****
if (DATETIME_NOT_FINITE(*datetime))
{
! #if FALSE
/* should return null but Postgres doesn't like that currently. - tgl 97/06/12 */
elog(ERROR, "Datetime is not finite", NULL);
#endif
--- 1412,1418 ----
if (DATETIME_NOT_FINITE(*datetime))
{
! #if NOT_USED
/* should return null but Postgres doesn't like that currently. - tgl 97/06/12 */
elog(ERROR, "Datetime is not finite", NULL);
#endif
***************
*** 1494,1500 ****
if (tm2datetime(tm, fsec, &tz, result) != 0)
elog(ERROR, "Unable to truncate datetime to '%s'", lowunits);
! #if FALSE
}
else if ((type == RESERV) && (val == DTK_EPOCH))
{
--- 1494,1500 ----
if (tm2datetime(tm, fsec, &tz, result) != 0)
elog(ERROR, "Unable to truncate datetime to '%s'", lowunits);
! #if NOT_USED
}
else if ((type == RESERV) && (val == DTK_EPOCH))
{
***************
*** 1552,1558 ****
if (TIMESPAN_IS_INVALID(*timespan))
{
! #if FALSE
elog(ERROR, "Timespan is not finite", NULL);
#endif
result = NULL;
--- 1552,1558 ----
if (TIMESPAN_IS_INVALID(*timespan))
{
! #if NOT_USED
elog(ERROR, "Timespan is not finite", NULL);
#endif
result = NULL;
***************
*** 1610,1616 ****
result = NULL;
}
! #if FALSE
}
else if ((type == RESERV) && (val == DTK_EPOCH))
{
--- 1610,1616 ----
result = NULL;
}
! #if NOT_USED
}
else if ((type == RESERV) && (val == DTK_EPOCH))
{
***************
*** 1678,1684 ****
if (DATETIME_NOT_FINITE(*datetime))
{
! #if FALSE
/* should return null but Postgres doesn't like that currently. - tgl 97/06/12 */
elog(ERROR, "Datetime is not finite", NULL);
#endif
--- 1678,1684 ----
if (DATETIME_NOT_FINITE(*datetime))
{
! #if NOT_USED
/* should return null but Postgres doesn't like that currently. - tgl 97/06/12 */
elog(ERROR, "Datetime is not finite", NULL);
#endif
***************
*** 1843,1849 ****
if (TIMESPAN_IS_INVALID(*timespan))
{
! #if FALSE
elog(ERROR, "Timespan is not finite", NULL);
#endif
*result = 0;
--- 1843,1849 ----
if (TIMESPAN_IS_INVALID(*timespan))
{
! #if NOT_USED
elog(ERROR, "Timespan is not finite", NULL);
#endif
*result = 0;
***************
*** 1893,1907 ****
break;
case DTK_DECADE:
! *result = (tm->tm_year / 10) + 1;
break;
case DTK_CENTURY:
! *result = (tm->tm_year / 100) + 1;
break;
case DTK_MILLENIUM:
! *result = (tm->tm_year / 1000) + 1;
break;
default:
--- 1893,1907 ----
break;
case DTK_DECADE:
! *result = (tm->tm_year / 10);
break;
case DTK_CENTURY:
! *result = (tm->tm_year / 100);
break;
case DTK_MILLENIUM:
! *result = (tm->tm_year / 1000);
break;
default:
***************
*** 2454,2460 ****
tm->tm_mday = tx->tm_mday;
tm->tm_hour = tx->tm_hour;
tm->tm_min = tx->tm_min;
! #if FALSE
/* XXX HACK
* Argh! My Linux box puts in a 1 second offset for dates less than 1970
* but only if the seconds field was non-zero. So, don't copy the seconds
--- 2454,2460 ----
tm->tm_mday = tx->tm_mday;
tm->tm_hour = tx->tm_hour;
tm->tm_min = tx->tm_min;
! #if NOT_USED
/* XXX HACK
* Argh! My Linux box puts in a 1 second offset for dates less than 1970
* but only if the seconds field was non-zero. So, don't copy the seconds
***************
*** 2814,2819 ****
--- 2814,2820 ----
int flen,
val;
int mer = HR24;
+ int haveTextMonth = FALSE;
int is2digits = FALSE;
int bc = FALSE;
***************
*** 2955,2968 ****
#ifdef DATEDEBUG
printf("DecodeDateTime- month field %s value is %d\n", field[i], val);
#endif
tm->tm_mon = val;
break;
- /*
- * daylight savings time modifier (solves "MET
- * DST" syntax)
- */
case DTZMOD:
tmask |= DTK_M(DTZ);
tm->tm_isdst = 1;
if (tzp == NULL)
--- 2956,2978 ----
#ifdef DATEDEBUG
printf("DecodeDateTime- month field %s value is %d\n", field[i], val);
#endif
+ /* already have a (numeric) month? then see if we can substitute... */
+ if ((fmask & DTK_M(MONTH)) && (! haveTextMonth)
+ && (!(fmask & DTK_M(DAY)))
+ && ((tm->tm_mon >= 1) && (tm->tm_mon <= 31)))
+ {
+ tm->tm_mday = tm->tm_mon;
+ tmask = DTK_M(DAY);
+ #ifdef DATEDEBUG
+ printf("DecodeNumber- misidentified month previously; assign as day %d\n", tm->tm_mday);
+ #endif
+ }
+ haveTextMonth = TRUE;
tm->tm_mon = val;
break;
case DTZMOD:
+ /* daylight savings time modifier (solves "MET DST" syntax) */
tmask |= DTK_M(DTZ);
tm->tm_isdst = 1;
if (tzp == NULL)
***************
*** 3466,3482 ****
*tmask = DTK_M(YEAR);
/* already have a year? then see if we can substitute... */
! if (fmask & DTK_M(YEAR))
{
! if ((!(fmask & DTK_M(DAY)))
! && ((tm->tm_year >= 1) && (tm->tm_year <= 31)))
! {
#ifdef DATEDEBUG
! printf("DecodeNumber- misidentified year previously; swap with day %d\n", tm->tm_mday);
#endif
- tm->tm_mday = tm->tm_year;
- *tmask = DTK_M(DAY);
- }
}
tm->tm_year = val;
--- 3476,3489 ----
*tmask = DTK_M(YEAR);
/* already have a year? then see if we can substitute... */
! if ((fmask & DTK_M(YEAR)) && (!(fmask & DTK_M(DAY)))
! && ((tm->tm_year >= 1) && (tm->tm_year <= 31)))
{
! tm->tm_mday = tm->tm_year;
! *tmask = DTK_M(DAY);
#ifdef DATEDEBUG
! printf("DecodeNumber- misidentified year previously; assign as day %d\n", tm->tm_mday);
#endif
}
tm->tm_year = val;