Re: text -> time cast problem
От | Tom Lane |
---|---|
Тема | Re: text -> time cast problem |
Дата | |
Msg-id | 29301.1007483923@sss.pgh.pa.us обсуждение исходный текст |
Ответ на | text -> time cast problem (Brent Verner <brent@rcfile.org>) |
Ответы |
Re: text -> time cast problem
|
Список | pgsql-hackers |
Brent Verner <brent@rcfile.org> writes: > I noticed an incorrect example in doc/src/sgml/func.sgml... > brent=# SELECT EXTRACT(SECOND FROM TIME '17:12:28.5'); > date_part > ----------- > 28 > (1 row) > The documentation says this should return 28.5. Historically we've made EXTRACT(SECOND) return integral seconds, with MILLISECOND/MICROSECOND field names for the fractional seconds. So the docs are incorrect with respect to the actual code behavior. But ... The SQL92 spec appears to intend that EXTRACT(SECOND) should return seconds *and* fractional seconds. In 6.6 syntax rule 4, 4) If <extract expression> is specified, then Case: a) If <datetime field> does not specify SECOND, then the data type of the result is exact numericwith implementation- defined precision and scale 0. b) Otherwise, the data type of the result is exact numeric with implementation-defined precision andscale. The implementation-defined scale shall not be less than the spec- ified or implied <timefractional seconds precision> or <in- terval fractional seconds precision>, as appropriate, of the SECOND <datetime field> of the <extract source>. It looks to me like 4b *requires* the fractional part of the seconds field to be returned. (Of course, we're blithely ignoring the aspect of this that requires an exact numeric result type, since our version of EXTRACT returns float8, but let's not worry about that fine point at the moment.) Don't think I want to change this behavior for 7.2, but it ought to be on the TODO list to fix it for 7.3. > Digging a bit, I > noticed the following (discrepancy?). Is this desired behavior? > brent=# select "time"('12:00:12.5'); > time > ------------- > 12:00:12.50 > (1 row) > brent=# select '12:00:12.5'::time; > time > ---------- > 12:00:12 > (1 row) > IMO, one of these needs to be fixed before RC1 is rolled. I'm not convinced that's broken. You're missing an important point (forgivable, because Thomas hasn't yet committed any documentation about it): TIME now implies a precision specification, and the default is TIME(0), ie no fractional digits. Observe: regression=# select '12:00:12.6'::time(0); time ----------12:00:13 (1 row) regression=# select '12:00:12.6'::time(2); time -------------12:00:12.60 (1 row) In the pseudo-function-call case, there is no implicit precision specification and thus the value does not get rounded. BTW, this means that SELECT EXTRACT(SECOND FROM TIME '17:12:28.5'); *should* return 28, because the TIME literal is implicitly TIME(0). But if it were written TIME(1) '17:12:28.5' or more precision, then I believe SQL92 requires the EXTRACT result to include the fraction. regards, tom lane
В списке pgsql-hackers по дате отправления:
Следующее
От: Peter EisentrautДата:
Сообщение: Re: FW: [CYGWIN] 7.2b3 postmaster doesn't start on Win98