Thread: select to_timestamp('02/26/2011 14:50', 'MM/DD/YYYY HH24MI')
Hi all -
I have recently upgraded from 8.3 to 9.0.2.
when I run the following sql in 9.0.2 " select to_timestamp('02/26/2011 14:50', 'MM/DD/YYYY HH24MI') " I am getting the following error and the sql runs fine in older version
Is it my installation issue or library missing?
ERROR: invalid value ":5" for "MI"
DETAIL: Value must be an integer.
********** Error **********
ERROR: invalid value ":5" for "MI"
SQL state: 22007
Detail: Value must be an integer.
Thanks for the help
Regards
On 02/25/2011 09:30 AM, akp geek wrote: > Hi all - > > I have recently upgraded from 8.3 to 9.0.2. > > when I run the following sql in 9.0.2 " select to_timestamp('02/26/2011 14:50', 'MM/DD/YYYY HH24MI') " I am gettingthe following > error and the sql runs fine in older version > > Is it my installation issue or library missing? > > > ERROR: invalid value ":5" for "MI" > DETAIL: Value must be an integer. > > ********** Error ********** > > ERROR: invalid value ":5" for "MI" > SQL state: 22007 > Detail: Value must be an integer. > > > > Thanks for the help > Regards military time doesn't have the ':' in the HHMM. look closely at your 'working' result, does it look like this? reporting=# select to_timestamp('02/26/2011 14:50', 'MM/DD/YYYY HH24MI'); to_timestamp ------------------------ 2011-02-26 14:00:00-05 (1 row) reporting=# select to_timestamp('02/26/2011 1450', 'MM/DD/YYYY HH24MI'); to_timestamp ------------------------ 2011-02-26 14:50:00-05 (1 row) Note the invalid time for the first result.
On Friday, February 25, 2011 6:41:54 am Reid Thompson wrote: > On 02/25/2011 09:30 AM, akp geek wrote: > > Hi all - > > > > I have recently upgraded from 8.3 to 9.0.2. > > > > when I run the following sql in 9.0.2 " select > > to_timestamp('02/26/2011 14:50', 'MM/DD/YYYY HH24MI') " I am getting the > > following error and the sql runs fine in older version > > > > Is it my installation issue or library missing? > > > > > > ERROR: invalid value ":5" for "MI" > > DETAIL: Value must be an integer. > > > > ********** Error ********** > > > > ERROR: invalid value ":5" for "MI" > > SQL state: 22007 > > Detail: Value must be an integer. > > > > > > > > Thanks for the help > > Regards > > military time doesn't have the ':' in the HHMM. > look closely at your 'working' result, does it look like this? > > > reporting=# select to_timestamp('02/26/2011 14:50', 'MM/DD/YYYY HH24MI'); > to_timestamp > ------------------------ > 2011-02-26 14:00:00-05 > (1 row) > > reporting=# select to_timestamp('02/26/2011 1450', 'MM/DD/YYYY HH24MI'); > to_timestamp > ------------------------ > 2011-02-26 14:50:00-05 > (1 row) > > Note the invalid time for the first result. To confirm above I ran your query on 8.3. Your original version: test=# select to_timestamp('02/26/2011 14:50', 'MM/DD/YYYY HH24MI'); to_timestamp ------------------------ 2011-02-26 14:00:00-08 (1 row) Corrected for the ':': test=# select to_timestamp('02/26/2011 14:50', 'MM/DD/YYYY HH24:MI'); to_timestamp ------------------------ 2011-02-26 14:50:00-08 (1 row) On 9.0.3 corrected for the ':': test(5432)aklaver=>select to_timestamp('02/26/2011 14:50', 'MM/DD/YYYY HH24:MI'); to_timestamp ------------------------ 2011-02-26 14:50:00-08 (1 row) Seems 9.0+ is stricter on its parsing. -- Adrian Klaver adrian.klaver@gmail.com
Adrian Klaver wrote: > test=# select to_timestamp('02/26/2011 14:50', 'MM/DD/YYYY HH24MI'); > to_timestamp > ------------------------ > 2011-02-26 14:00:00-08 > (1 row) > > Corrected for the ':': > > test=# select to_timestamp('02/26/2011 14:50', 'MM/DD/YYYY HH24:MI'); > to_timestamp > ------------------------ > 2011-02-26 14:50:00-08 > (1 row) > > > On 9.0.3 corrected for the ':': > > test(5432)aklaver=>select to_timestamp('02/26/2011 14:50', 'MM/DD/YYYY > HH24:MI'); > to_timestamp > ------------------------ > 2011-02-26 14:50:00-08 > (1 row) > > > Seems 9.0+ is stricter on its parsing. Agreed. We have made some cleanups to the to_timestamp code in recent major releases and that is what the user must be seeing. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Thank you all for the suggestion and it's very clear to me now
Regards
On Fri, Feb 25, 2011 at 10:02 AM, Bruce Momjian <bruce@momjian.us> wrote:
Adrian Klaver wrote:Agreed. We have made some cleanups to the to_timestamp code in recent
> test=# select to_timestamp('02/26/2011 14:50', 'MM/DD/YYYY HH24MI');
> to_timestamp
> ------------------------
> 2011-02-26 14:00:00-08
> (1 row)
>
> Corrected for the ':':
>
> test=# select to_timestamp('02/26/2011 14:50', 'MM/DD/YYYY HH24:MI');
> to_timestamp
> ------------------------
> 2011-02-26 14:50:00-08
> (1 row)
>
>
> On 9.0.3 corrected for the ':':
>
> test(5432)aklaver=>select to_timestamp('02/26/2011 14:50', 'MM/DD/YYYY
> HH24:MI');
> to_timestamp
> ------------------------
> 2011-02-26 14:50:00-08
> (1 row)
>
>
> Seems 9.0+ is stricter on its parsing.
major releases and that is what the user must be seeing.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general