Jerry; When I run the query you supplied, with my database
select sli.ts::timestamptz as tstamp from public.sql_log_import sli where sli.id <= 10; I get the following error: ERROR: invalid input syntax for type timestamp with time zone: "08/06/2014 03:08:58" ********** Error ********** ERROR: invalid input syntax for type timestamp with time zone: "08/06/2014 03:08:58" SQL state: 22007 Thanks Lou -----Original Message----- From: Jerry Sievers [mailto:gsiever...@comcast.net] Sent: Monday, September 08, 2014 2:31 PM To: Lou Oquin Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Issue with to_timestamp function Lou Oquin <loq...@nammotalley.com> writes: > Ive 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'; > > Heres 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. Ok but why not you just cast since the input data is compatible anyway, at least from what I saw up there... sj$ psql -efq --no-psqlrc begin; BEGIN create temp table foo as select '08/06/2014 03:08:58'::text as ts; SELECT 1 Table "pg_temp_7.foo" Column | Type | Modifiers --------+------+----------- ts | text | select ts::timestamptz from foo; ts ------------------------ 2014-08-06 03:08:58-05 (1 row) sj$ > > Heres what Im 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 OQuin > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
