Search Postgresql Archives

Re: Issue with to_timestamp function

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

 



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