Thanks for your help, Adrian.
Had a fire to put out before I left for home yesterday, and did not see the replies from you, Melvin Davidson and
JerrySievers until this morning. I read the most recent (yours) first) and ran the query in psql; it complained about
UTF8encoding characters in the data. Then dug into the raw data and found there were three hi-bit characters in front
ofthe '0' on the first record. Replaced the first records date with the second records 'identical' (but without the
addedcharacters) and the timestamp casting now works as expected.
Then, when I read Jerry's reply, saw that he had spotted it late yesterday afternoon.
It is a sql server log file that I'm importing into my local database; I'm using pg in analyzing the log data.
I apparently selected ascii instead of UTF8 encoding when I imported the sql server log file with pgadmin...
Thanks again.
Lou
-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Monday, September 08, 2014 6:04 PM
To: Lou Oquin; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Issue with to_timestamp function
On 09/08/2014 01:52 PM, Lou Oquin wrote:
> I've imported a csv export of an MS SQL Server log file into a staging
> table on my local install of Postgresql (9.3/UTF8 encoding) for analysis.
>
>
> select to_timestamp(ts, 'MM/DD/YYYY hh24:mi:ss')::timestamp with time
> zone as tStamp
>
> from sql_log_import
>
> where id <= 10
>
> ********** Error **********
>
> SQL state: 22007
>
> Detail: Value must be an integer.
>
> Any Ideas?
To get that error I had to do something like this:
hplc=> select to_timestamp('aug/06/2014 03:08:58 ', 'MM/DD/YYYY hh24:mi:ss');
ERROR: invalid value "au" for "MM"
DETAIL: Value must be an integer.
So at a guess, the data being imported has some month abbreviations in it.
>
> Thanks
>
> *Lou O'Quin*
>
--
Adrian Klaver
adrian.klaver@aklaver.com