Thread: Rounding issue with current_time

With current CVS, I did

regression=# create table foo (f1 date default current_date,
regression(# f2 time default current_time,
regression(# f3 timestamp default current_timestamp);
CREATE
regression=# \d foo                            Table "foo"Column |           Type           |            Modifiers
--------+--------------------------+----------------------------------f1     | date                     | default
date('now'::text)f2    | time                     | default "time"('now'::text)f3     | timestamp with time zone |
default"timestamp"('now'::text)
 

regression=# insert into foo default values;
INSERT 139633 1
regression=# insert into foo default values;
INSERT 139634 1
regression=# select * from foo;    f1     |    f2    |           f3
------------+----------+------------------------2001-10-03 | 13:15:37 | 2001-10-03 13:15:37-042001-10-03 | 13:15:49 |
2001-10-0313:15:50-04
 
(2 rows)


It's fairly disconcerting that f2 and f3 don't agree, wouldn't you say?
Further experimentation shows that it happens about half the time, with
the timestamp always one second ahead of the time when they differ.
I infer that the new sub-second-resolution transaction timestamp is
being correctly rounded when stored as a timestamp, but is truncated not
rounded when stored as a time.  Type timetz shows the same misbehavior.
Not sure where to look for this ...
        regards, tom lane

Further experimentation:

regression=# create table foo3 (f1 date default current_date,
regression(# f2 time(3) default current_time,
regression(# f3 timestamp(3) default current_timestamp);
CREATE
regression=# insert into foo3 default values;
(multiple times)
regression=# select * from foo3;    f1     |    f2    |             f3
------------+----------+-----------------------------2001-10-03 | 13:32:07 | 2001-10-03 13:32:07-042001-10-03 |
13:32:08| 2001-10-03 13:32:08.3020-042001-10-03 | 13:32:09 | 2001-10-03 13:32:09.4280-042001-10-03 | 13:32:10 |
2001-10-0313:32:10.2530-042001-10-03 | 13:32:10 | 2001-10-03 13:32:10.8850-042001-10-03 | 13:32:11 | 2001-10-03
13:32:11.2930-042001-10-03| 13:32:11 | 2001-10-03 13:32:11.6650-042001-10-03 | 13:32:12 | 2001-10-03
13:32:12.04-042001-10-03| 13:32:13 | 2001-10-03 13:32:13.3730-04
 
(9 rows)

So the real issue appears to be that subsecond resolution isn't
propagating into time and timetz at all.
        regards, tom lane

...
> It's fairly disconcerting that f2 and f3 don't agree, wouldn't you say?

:) I'll look at it.
                     - Thomas

BTW, would you object to my removing the macros
IS_BUILTIN_TYPE(), IS_HIGHER_TYPE(), IS_HIGHEST_TYPE()
from parse_coerce.h?  They are used nowhere and are not
being maintained --- eg, they don't seem to know about
TIMESTAMPTZ.
        regards, tom lane

...
> So the real issue appears to be that subsecond resolution isn't
> propagating into time and timetz at all.

Ah. Of course it isn't, because I (probably) didn't change
DecodeTimeOnly() to use the microsecond resolution version of the
transaction time. Will look at it.
                       - Thomas

> BTW, would you object to my removing the macros
> IS_BUILTIN_TYPE(), IS_HIGHER_TYPE(), IS_HIGHEST_TYPE()
> from parse_coerce.h?  They are used nowhere and are not
> being maintained --- eg, they don't seem to know about
> TIMESTAMPTZ.

OK. I had already stripped out some "#if NOT_USED" code but must have
missed those.
                     - Thomas