The data is ts 08/06/2014 03:08:58 08/06/2014 03:08:58 08/06/2014 03:08:58
Thanks Lou From: Melvin Davidson [mailto:melvin6...@gmail.com] Sent: Monday, September 08, 2014 2:30 PM To: Lou Oquin Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Issue with to_timestamp function I suspect your data is not what you think it is. What do you see when you do SELECT ts FROM from sql_log_import LIMIT 3; On Mon, Sep 8, 2014 at 4:52 PM, Lou Oquin <loq...@nammotalley.com<mailto:loq...@nammotalley.com>> 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? Thanks Lou O’Quin -- Melvin Davidson I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you. [http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif]
