Thread: timestamp resolution?
Is this an expected behavior? I could not see why t1 and t2 are showing different time resolutions... test=# create table t3(t1 timestamp(2), t2 timestamp(2) default current_timestamp); CREATE test=# insert into t3 values(current_timestamp); INSERT 16566 1 test=# select * from t3; t1 | t2 ------------------------+---------------------------2001-10-04 13:48:34+09 | 2001-10-04 13:48:34.34+09 (1 row) -- Tatsuo Ishii
> Is this an expected behavior? I could not see why t1 and t2 are
> showing different time resolutions...
Even stranger, this only happens on the first call to CURRENT_TIMESTAMP
after starting a backend (example below), and stays that way if I just
do "select current_timestamp". Something must not be initialized quite
right, but I don't know what. Any guesses?
- Thomas
(backend already connected and have just dropped t1)
thomas=# create table t1 (d1 timestamp(2), d2 timestamp(2) default
current_timestamp);
CREATE
thomas=# insert into t1 values (current_timestamp);
INSERT 16572 1
thomas=# select * from t1; d1 | d2
---------------------------+---------------------------2001-10-04 05:37:12.09+00 | 2001-10-04 05:37:12.09+00
(1 row)
thomas=# \q
myst$ psql
...
thomas=# insert into t1 values (current_timestamp);
INSERT 16573 1
thomas=# select * from t1; d1 | d2
---------------------------+---------------------------2001-10-04 05:37:12.09+00 | 2001-10-04 05:37:12.09+002001-10-04
05:37:40+00 | 2001-10-04 05:37:39.72+00
(2 rows)
thomas=# insert into t1 values (current_timestamp);
INSERT 16574 1
thomas=# select * from t1; d1 | d2
---------------------------+---------------------------2001-10-04 05:37:12.09+00 | 2001-10-04 05:37:12.09+002001-10-04
05:37:40+00 | 2001-10-04 05:37:39.72+002001-10-04 05:38:08.33+00 | 2001-10-04 05:38:08.33+00
(3 rows)
Thomas Lockhart <lockhart@fourpalms.org> writes:
> Even stranger, this only happens on the first call to CURRENT_TIMESTAMP
> after starting a backend (example below), and stays that way if I just
> do "select current_timestamp". Something must not be initialized quite
> right, but I don't know what. Any guesses?
Ah, I've got it. Two problems: AdjustTimestampForTypmod is one brick
shy of a load, and the hardwired calls to timestamp_in and friends
weren't passing all the parameters they should. (Can anyone think of
a way for DirectFunctionCall to do any checking?)
Patch will be committed in a moment...
regards, tom lane
...
> Ah, I've got it. Two problems: AdjustTimestampForTypmod is one brick
> shy of a load, and the hardwired calls to timestamp_in and friends
> weren't passing all the parameters they should. (Can anyone think of
> a way for DirectFunctionCall to do any checking?)
OK, I found the second item last night, but am not sure why
AdjustTimestampForTypmod needs more fixes.
I'm going through gram.y and fixing up the implementations of
CURRENT_TIMESTAMP et al. One point folks will run into is that
CURRENT_TIMESTAMP *should* return time to the second, not fractions
thereof, and CURRENT_TIMESTAMP(p) should be used to get something more
precise. Another issue I just noticed is that the result of
create table t1 (d timestamp(2) default current_timestamp);
gives me two decimal points of fractional seconds (after fixups for
Tatsuo's reported troubles) but I would think that it should round to
the second. Looks like we are "type folding" past the typmod attributes.
Comments?
- Thomas
Thomas Lockhart <lockhart@fourpalms.org> writes:
> I'm going through gram.y and fixing up the implementations of
> CURRENT_TIMESTAMP et al. One point folks will run into is that
> CURRENT_TIMESTAMP *should* return time to the second, not fractions
> thereof, and CURRENT_TIMESTAMP(p) should be used to get something more
> precise. Another issue I just noticed is that the result of
> create table t1 (d timestamp(2) default current_timestamp);
> gives me two decimal points of fractional seconds (after fixups for
> Tatsuo's reported troubles) but I would think that it should round to
> the second. Looks like we are "type folding" past the typmod attributes.
No, it's just that CURRENT_TIMESTAMP doesn't presently reduce its
precision, as you assert it should do. However, I see nothing in SQL99
6.19 that asserts anything about the precision of CURRENT_TIMESTAMP
without a precision indicator. It just says
2) If specified, <time precision> and <timestamp precision> respectively determine the precision of
thetime or timestamp value returned.
which seems to leave it up to us to choose the behavior when no
precision is specified. I'd prefer to see CURRENT_TIMESTAMP return as
much precision as possible (see also previous message).
BTW, CURRENT_TIME and CURRENT_TIMESTAMP should return TIMETZ and
TIMESTAMPTZ respectively, but currently do not --- are you fixing that?
regards, tom lane
> No, it's just that CURRENT_TIMESTAMP doesn't presently reduce its
> precision, as you assert it should do. However, I see nothing in SQL99
> 6.19 that asserts anything about the precision of CURRENT_TIMESTAMP
> without a precision indicator. It just says
> 2) If specified, <time precision> and <timestamp precision>
> respectively determine the precision of the time or timestamp
> value returned.
> which seems to leave it up to us to choose the behavior when no
> precision is specified. I'd prefer to see CURRENT_TIMESTAMP return as
> much precision as possible (see also previous message).
Hmm. Somewhere else it *does* specify a precision of zero for TIME and
TIMESTAMP; wonder why that rule wouldn't apply to CURRENT_TIME etc too?
Not that lots of precision isn't good, but I'd like to be consistant.
> BTW, CURRENT_TIME and CURRENT_TIMESTAMP should return TIMETZ and
> TIMESTAMPTZ respectively, but currently do not --- are you fixing that?
Yup. Though I'm not certain that it would effectively be any different.
- Thomas
...
> Hmm. Somewhere else it *does* specify a precision of zero for TIME and
> TIMESTAMP; wonder why that rule wouldn't apply to CURRENT_TIME etc too?
> Not that lots of precision isn't good, but I'd like to be consistant.
Ah, I'd forgotten about the 6 vs 0 behaviors (but had them in the code
just a couple of days ago ;)
- Thomas