Re: Issue with to_timestamp function

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:gsievers19@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 <LOquin@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/UTF8encoding) 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
informationalmessage 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
WindowsEvents 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
MicrosoftDistributed Transaction Coordinator (MS DTC) will begin once the connection is re-established. This is an
informationalmessage 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
messageonly. 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
keepgetting 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.consulting@comcast.net
p: 312.241.7800