to_date() not works as described for pattern DD & HH

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

Responses

Browse pgsql-bugs by date

  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