to_date() not works as described for pattern DD & HH
The Funktion ist described as
to_date(text, text) date convert string to date to_date('05 Dec 2000', 'YYYYMMDD')
The 2 Pattern DD/HH are not well working
All Digs ar counted from Position6/8 are counted as Days or Hour
and produce a future Date or error from a Timestamp in Milliseconds
The description
DD day of month (01-31)
HH hour of day (01-12)
Fore Example:
- Full Time in include ms 2015-12-02 11:59:09.001
-- Fulltimestamp 20151202115909001
SELECT to_date('20151202115909001','YYYYMMDD') AS der_2_december_eleven_a_clock
-> "5795177-12-29“ => fare away in the future
SELECT to_date('20151202115909001','YYYYMMDDHH') AS der_2_december_eleven_a_clock
ERROR: hour "115909001" is invalid for the 12-hour clock
HINT: Use the 24-hour clock, or give an hour between 1 and 12.
********** Fehler **********
ERROR: hour "115909001" is invalid for the 12-hour clock
SQL Status:22007
Hinweis:Use the 24-hour clock, or give an hour between 1 and 12.
- Full Time in include ms 2015-12-01 11:59:09.001
-- Fulltimestamp 20151222115909001
SELECT to_date('20151222115909001','YYYYMMDD') AS 22_december_eleven_a_clock
ERROR: value for "DD" in source string is out of range
DETAIL: Value must be in the range -2147483648 to 2147483647.
********** Fehler **********
ERROR: value for "DD" in source string is out of range
SQL Status:22008
Detail:Value must be in the range -2147483648 to 2147483647.
The other Pattern works as Described
SELECT to_date('20151202115909001','YYYYMMDDHH24') AS der_2_december_eleven_a_clock
-> "2015-12-02"
My first workaround in the script was to take only the first 8 Digs
SELECT to_date(left(‚20151222115909001‘,8),'YYYYMMDD') AS 22_december_eleven_a_clock
"2015-12-22"
other description of the timestamp works also fine
SELECT to_date(left('20151202115909001',8),'YYYYMMDDHH24MISSMS') AS der_first_december_eleven_a_clock
SELECT to_date('20151202115909001','YYYYMMDDHH24MI') AS der_2_december_eleven_a_clock
Mit freundlichen Grüßen
Andreas.Rhode@gmx.de
Andreas Rhode <Andreas.Rhode@gmx.de> writes:
The 2 Pattern DD/HH are not well working
All Digs ar counted from Position6/8 are counted as Days or Hour
and produce a future Date or error from a Timestamp in Milliseconds
As far as I can see, the problem here is that you're not giving a pattern
that accurately describes the input data.
SELECT to_date('20151202115909001','YYYYMMDD') AS der_2_december_eleven_a_clock
-> "5795177-12-29“ => fare away in the future
A correct format for this input would be something like YYYYMMDDHH24MISSMS:
# SELECT to_timestamp('20151202115909001','YYYYMMDDHH24MISSMS');
to_timestamp
----------------------------
2015-12-02 11:59:09.001-05
(1 row)
I think what is happening in your example is that it's including all the
remaining adjacent digits in the DD field. While this seems silly here,
it's less silly in, for example,
# SELECT to_date('201512021','YYYYMMDD');
to_date
------------
2015-12-21
(1 row)
But it's not really to_date's job to parse input that is not correctly
described by the given format.
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