Re: [BUGS] Return value error of‘to_timestamp’

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: [BUGS] Return value error of‘to_timestamp’
Дата
Msg-id CAKFQuwYMf7Xb9yqwTMcJ67-+r7V88NkR=wKpskFM6Hykm4ZhZQ@mail.gmail.com
обсуждение исходный текст
Ответ на Return value error of‘to_timestamp’  (甄明洋 <zhenmingyang@yeah.net>)
Ответы Re: Re: [BUGS] Return value error of‘to_timestamp’
Список pgsql-bugs
Дерево обсуждения
On Thu, Aug 4, 2016 at 12:51 AM, 甄明洋 <zhenmingyang@yeah.net> wrote:
When the fractional seconds more than 6, the return value of to_timestamp without truncation.
Postgres think the total value of the.123456789 as a fractional second part and convert to microsecond stored in database.
 example:
postgres=# select to_timestamp('1990-1-1 11:11:11.123456789', 'YYYY-MM-DD HH24:MI:SS.US');
         to_timestamp
-------------------------------
 1990-01-01 11:13:14.456789+08
(1 row)
postgres=#


​Working as designed...​its suggested to use data type casting whenever possible to avoid this issue.


​"""
 to_timestamp and to_date exist to handle input formats that cannot be converted by simple casting. These functions interpret input liberally, with minimal error checking. While they produce valid output, the conversion can yield unexpected results. For example, input to these functions is not restricted by normal ranges, thus to_date('20096040','YYYYMMDD') returns 2014-01-17 rather than causing an error. Casting does not have this behavior.
​"""​
​
This may also be relevant...I'm not personally familiar with the usage of this function​.

"""
In a conversion from string to timestamp, millisecond (MS) or microsecond (US) values are used as the seconds digits after the decimal point. For example to_timestamp('12:3', 'SS:MS') is not 3 milliseconds, but 300, because the conversion counts it as 12 + 0.3 seconds. This means for the format SS:MS, the input values 12:3, 12:30, and 12:300 specify the same number of milliseconds. To get three milliseconds, one must use 12:003, which the conversion counts as 12 + 0.003 = 12.003 seconds.

Here is a more complex example: to_timestamp('15:12:02.020.001230', 'HH:MI:SS.MS.US') is 15 hours, 12 minutes, and 2 seconds + 20 milliseconds + 1230 microseconds = 2.021230 seconds.
"""

David J.

В списке pgsql-bugs по дате отправления:

Предыдущее
От: "klimych@tut.by"
Дата:
Сообщение: Re: BUG #14275: cursor's variable in pgsql doesn't respect scope
Следующее
От: Pavel Stehule
Дата:
Сообщение: Re: BUG #14275: cursor's variable in pgsql doesn't respect scope