neilc=# select '1 day 1 millisecond'::interval;
interval
--------------------
1 day 00:00:00.001
(1 row)
neilc=# select '1 millisecond'::interval;
ERROR: invalid input syntax for type interval: "1 millisecond"
neilc=# select '0.001 seconds'::interval;
interval
--------------
00:00:00.001
(1 row)
Similarly for "microsecond".
I think allowing the first input but not the second is pretty
inconsistent. Attached is a patch that fixes this for both milliseconds
and microseconds. The previous coding allowed "x seconds y milliseconds"
to be specified, so this patch preserves that behavior. It *also* allows
"x seconds y seconds" as valid input, although you could make a
consistency argument for that anyway: (with CVS HEAD, unpatched)
neilc=# select '5 days 10 days'::interval;
interval
----------
15 days
neilc=# select '1 week 2 week 3 weeks'::interval;
interval
----------
42 days
neilc=# select '1 second 2 second'::interval;
ERROR: invalid input syntax for type interval: "1 second 2 second"
Is there any reason to why DecodeInterval() is willing to accept
multiple specifications for some time units but not others?
-Neil
Index: src/backend/utils/adt/datetime.c
===================================================================
RCS file: /home/neilc/postgres/cvs_root/pgsql/src/backend/utils/adt/datetime.c,v
retrieving revision 1.179
diff -p -c -r1.179 datetime.c
*** src/backend/utils/adt/datetime.c 27 May 2007 20:32:16 -0000 1.179
--- src/backend/utils/adt/datetime.c 28 May 2007 04:37:15 -0000
*************** DecodeInterval(char **field, int *ftype,
*** 2805,2810 ****
--- 2805,2811 ----
#else
*fsec += (val + fval) * 1e-6;
#endif
+ tmask = (fmask & DTK_M(SECOND)) ? 0 : DTK_M(SECOND);
break;
case DTK_MILLISEC:
*************** DecodeInterval(char **field, int *ftype,
*** 2813,2818 ****
--- 2814,2820 ----
#else
*fsec += (val + fval) * 1e-3;
#endif
+ tmask = (fmask & DTK_M(SECOND)) ? 0 : DTK_M(SECOND);
break;
case DTK_SECOND:
*************** DecodeInterval(char **field, int *ftype,
*** 2822,2828 ****
#else
*fsec += fval;
#endif
! tmask = DTK_M(SECOND);
break;
case DTK_MINUTE:
--- 2824,2830 ----
#else
*fsec += fval;
#endif
! tmask = (fmask & DTK_M(SECOND)) ? 0 : DTK_M(SECOND);
break;
case DTK_MINUTE:
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
