Search Postgresql Archives

Re: Issue with to_timestamp function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux