From: | Andreas Rhode <Andreas(dot)Rhode(at)gmx(dot)de> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Cc: | "Rhode,A(dot),SNL IT BRIEF,2520,DA,Berater" <andreas(dot)rhode(at)deutschepost(dot)de> |
Subject: | to_date() not works as described for pattern DD & HH |
Date: | 2015-12-18 14:25:26 |
Message-ID: | 4C903B6A-2FE0-4164-9633-50581F1B58CA@gmx.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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(dot)Rhode(at)gmx(dot)de
From | Date | Subject | |
---|---|---|---|
Next Message | michal.schwarz | 2015-12-18 14:33:24 | BUG #13827: planner chooses more expensive plan than it should |
Previous Message | George Wesington | 2015-12-18 13:17:24 | Re: BUG #13823: Database crash - import requests in plpython3u |