I'm executing the query in pgAdmin3, in a SQL query window. The results are coming from the history tab of the output pane. Thanks Lou -----Original Message----- From: Adrian Klaver [mailto:adrian.klaver@xxxxxxxxxxx] Sent: Monday, September 08, 2014 2:47 PM To: Lou Oquin; pgsql-general@xxxxxxxxxxxxxx Subject: Re: 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. > > The staging table definition is: > > CREATE TABLE sql_log_import > > ( > > id serial NOT NULL, > > ts text, -- will convert to ts when merging into sql_server_logs > > source character varying(30), > > severity character varying(20), > > message text, > > CONSTRAINT sql_log_import_pk PRIMARY KEY (id) > > ) > > WITH ( > > OIDS=FALSE > > ); > > ALTER TABLE sql_log_import > > OWNER TO postgres; > > COMMENT ON COLUMN sql_log_import.ts IS 'will convert to ts when > merging into sql_server_logs'; > > Here's a copy of the first few lines of the data imported to table > sql_log_import: > > 08/06/2014 03:08:58,spid4s,Unknown,SQL Trace was stopped due to server > shutdown. Trace ID = '1'. This is an informational message only; no > user action is required. > > 08/06/2014 03:08:58,spid12s,Unknown,The current event was not reported > to the Windows Events log. Operating system error = 1717(The interface > is unknown.). You may need to clear the Windows Events log if it is full. > > 08/06/2014 03:08:58,spid12s,Unknown,Error: 17054<c/> Severity: 16<c/> > State: 1. > > 08/06/2014 03:08:58,Server,Unknown,The connection has been lost with > Microsoft Distributed Transaction Coordinator (MS DTC). Recovery of > any in-doubt distributed transactions involving Microsoft Distributed > Transaction Coordinator (MS DTC) will begin once the connection is > re-established. This is an informational message only. No user action > is required. > > 08/06/2014 03:08:58,spid12s,Unknown,Service Broker manager has shut down. > > 08/06/2014 03:08:55,Server,Unknown,SQL Server is terminating because > of a system shutdown. This is an informational message only. No user > action is required. > > The final table is very similar, but with a timestamp with timezone > field for the logged server data. But, when I try to populate the > target table with data from the staging table, I keep getting an error. > The issue is associated with the to_timestamp function. > > Here's what I'm seeing: If I use to_timestamp with the text data > (copied from table sql_log_import.ts), the select statement returns a > timestamp with timezone, as expected: > > -- Executing query: > > select to_timestamp('08/06/2014 03:08:58', 'MM/DD/YYYY > hh24:mi:ss')::timestamp with time zone as tstamp > > Total query runtime: 78 ms. > > 1 row retrieved. > > But, when I select data from the table sql_log_import, I get an error: > > -- Executing query: > > 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? Where are you getting this error? Or to put it another way, where are you executing the query? > > Thanks > > *Lou O'Quin* > -- Adrian Klaver adrian.klaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
