Return value error of‘to_timestamp’
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=#
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.
https://www.postgresql.org/docs/9.6/static/functions-formatting.html
"""
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.
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...
Maybe it means that current design is poor. However since this behavior
is documented there are users who might depend on it. So I doubt it
will be changed any time soon.
Though I wonder maybe we should consider introducing a new set of
time-related procedures with different behavior (to_timestamp_strict?),
that would be more obvious to users.
--
Best regards,
Aleksander Alekseev
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On Thu, Aug 4, 2016 at 11:03 AM, Aleksander Alekseev <
a.alekseev@postgrespro.ru> wrote:
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...
Maybe it means that current design is poor. However since this behavior
is documented there are users who might depend on it. So I doubt it
will be changed any time soon.Though I wonder maybe we should consider introducing a new set of
time-related procedures with different behavior (to_timestamp_strict?),
that would be more obvious to users.
Discussions ongoing...
an archive search should be fruitful.
David J.
Thank you very much !
I have another question for datetime type, it is GUC parameter 'timezone' , example as follow:
postgres=# set timezone='-08:00';
SET
postgres=# show timezone;
TimeZone
----------
-08:00
(1 row)
postgres=# select timestamptz'1990-1-1 11:11:11.123 -08:00' ;
timestamptz
----------------------------
1990-01-02 03:11:11.123+08
(1 row)
postgres=# select timestamptz'1990-1-1 11:11:11.123 +08:00' ;
timestamptz
----------------------------
1990-01-01 11:11:11.123+08
(1 row)
postgres=#
this looks like, the sign plus and minus are inverted, i dont understand why?
在 2016-08-04 23:06:40，"David G. Johnston" <david.g.johnston@gmail.com> 写道：
On Thu, Aug 4, 2016 at 11:03 AM, Aleksander Alekseev <a.alekseev@postgrespro.ru> wrote:
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...
Maybe it means that current design is poor. However since this behavior
is documented there are users who might depend on it. So I doubt it
will be changed any time soon.
Though I wonder maybe we should consider introducing a new set of
time-related procedures with different behavior (to_timestamp_strict?),
that would be more obvious to users.
Discussions ongoing...
an archive search should be fruitful.
David J.
=?UTF-8?B?55SE5piO5rSL?= <zhenmingyang@yeah.net> writes:
this looks like, the sign plus and minus are inverted, i dont understand why?
Time zone names follow the POSIX convention (plus is west of Greenwich).
Timestamp I/O follows the ISO convention (plus is east of Greenwich).
Aren't standards fun?
See para starting "One should be wary ..." here:
https://www.postgresql.org/docs/9.5/static/datatype-datetime.html#DATATYPE-TIMEZONES
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Aha i got it. thanks.
Why don't use a unified time zone Convention ?
If user is not particularly familiar with the document,
Maybe will feel confused.
At 2016-08-17 21:49:38, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
Show quoted text
=?UTF-8?B?55SE5piO5rSL?= <zhenmingyang@yeah.net> writes:
this looks like, the sign plus and minus are inverted, i dont understand why?
Time zone names follow the POSIX convention (plus is west of Greenwich).
Timestamp I/O follows the ISO convention (plus is east of Greenwich).
Aren't standards fun?See para starting "One should be wary ..." here:
https://www.postgresql.org/docs/9.5/static/datatype-datetime.html#DATATYPE-TIMEZONESregards, tom lane
Top posting is not the usual in this lists, anyway:
On Thu, Aug 18, 2016 at 11:57 AM, 甄明洋 <zhenmingyang@yeah.net> wrote:
Why don't use a unified time zone Convention ?
Given Tom said:
Aren't standards fun?
I suspect SQL std. mandates it.
SQL is really weird, and most weirdness is standard mandated. AAMOF I
normally find the standard extensions in postgres much easier to use
than the standard mandated parts.
Francisco Olarte.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Francisco Olarte <folarte@peoplecall.com> writes:
On Thu, Aug 18, 2016 at 11:57 AM, 甄明洋 <zhenmingyang@yeah.net> wrote:
Why don't use a unified time zone Convention ?
Given Tom said:
Aren't standards fun?
I suspect SQL std. mandates it.
The SQL standard does mandate use of ISO convention for timestamp values.
However, the use of any sort of timezone name in "SET timezone" is outside
the SQL standard (or at least it was last I looked). Our timezone name
support is based on the IANA (nee Olson) timezone data set, which is used
by just about everybody except Microsoft, and that follows the POSIX
standard.
In principle we could hack up the IANA code and data so that zone names
that look like POSIX names follow the ISO sign convention, but if you
ask me that's just nuts. It would mean for example that "set timezone
to 'PST8PDT'" inside PG would act completely differently from "TZ=PST8PDT"
in the shell. That would result in more confusion not less.
In short, neither of these choices were made in a vacuum.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
thanks for you answer
thanks !
在 2016-08-18 22:09:06，"Tom Lane" <tgl@sss.pgh.pa.us> 写道：
Show quoted text
Francisco Olarte <folarte@peoplecall.com> writes:
On Thu, Aug 18, 2016 at 11:57 AM, 甄明洋 <zhenmingyang@yeah.net> wrote:
Why don't use a unified time zone Convention ?
Given Tom said:
Aren't standards fun?
I suspect SQL std. mandates it.
The SQL standard does mandate use of ISO convention for timestamp values.
However, the use of any sort of timezone name in "SET timezone" is outside
the SQL standard (or at least it was last I looked). Our timezone name
support is based on the IANA (nee Olson) timezone data set, which is used
by just about everybody except Microsoft, and that follows the POSIX
standard.In principle we could hack up the IANA code and data so that zone names
that look like POSIX names follow the ISO sign convention, but if you
ask me that's just nuts. It would mean for example that "set timezone
to 'PST8PDT'" inside PG would act completely differently from "TZ=PST8PDT"
in the shell. That would result in more confusion not less.In short, neither of these choices were made in a vacuum.
regards, tom lane