Re: timestamp_part() bug?
От | Thomas Lockhart |
---|---|
Тема | Re: timestamp_part() bug? |
Дата | |
Msg-id | 3C9281B1.17E084D0@fourpalms.org обсуждение исходный текст |
Ответ на | Re: timestamp_part() bug? (Tatsuo Ishii <t-ishii@sra.co.jp>) |
Список | pgsql-hackers |
> There is a problem with epoch as well that was not in the 7.1.3 Hmm. 7.1.x did not implement any date_part() functions for time types. So the results were obtained from a conversion to interval before calling date_part()! 7.2 implements date_part() for time with time zone, and converts time without time zone to time with time zone when executing your query. The behavior is likely to be somewhat different. But... I think that your problem report now has two parts: 1) extract(epoch from time with time zone '00:00:34') should return something "reasonable". I'll claim that it does that currently, since (if you were trying that query) you are one hour away from GMT and get 3600+34 seconds back, which is consistant with same instant in GMT. If the epoch is relative to GMT, then this may be The Right Thing To Do. 2) extract(epoch from time '00:00:34') should return something which does not involve a time zone of any kind if it were following the conventions used for timestamp without time zone. So we should have an explicit function to do that, rather than relying on converting to "time with time zone" before extracting the "epoch". Unfortunately, I can't put a new function into 7.2.x due to the long-standing rule of not modifying system tables in minor upgrades. So solving (2) completely needs to wait for 7.3. You can work around this mis-feature for now by patching 7.2.x, replacing one of the definitions for date_part in src/include/catalog/pg_proc.h, oid = 1385 with the following: select date_part($1, cast((cast($2 as text) || ''+00'') as time with time zone)); Or, it seems that you can actually drop and replace this built-in function (I vaguely recall that there used to be problems with doing this, but it sure looks like it works!): thomas=# drop function date_part(text,time); DROP thomas=# create function date_part(text,time) returns double precision as ' thomas'# select date_part($1, cast((cast($2 as text) || ''+00'') as time with time zone)); thomas'# ' language 'sql'; CREATE thomas=# select extract(epoch from time '00:00:34');date_part ----------- 34 In looking at this issue I did uncover a bug in moving time with time zones to other time zones: thomas=# select timetz(interval '01:00', time with time zone '08:09:10-08'); timetz ----------------00:00:00.00+01 after repairing the offending code in timetz_izone() it seems to do the right thing: thomas=# select timetz(interval '01:00', time with time zone '08:09:10-08'); timetz -------------17:09:10+01 This last issue will be fixed in 7.2.1. And the function will be renamed to "timezone()" in 7.3 to be consistant with similar functions for other data types. - Thomas
В списке pgsql-hackers по дате отправления: