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

Started by Andreas Rhodeover 10 years ago2 messagesbugs
Jump to latest
#1Andreas Rhode
Andreas.Rhode@gmx.de

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Andreas Rhode (#1)
Re: to_date() not works as described for pattern DD & HH

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