Обсуждение: Issue with to_timestamp function

Поиск

Список

Период

Сортировка

Issue with to_timestamp function

От

Lou Oquin

Дата:

09 сентября 2014 г., 00:09:48

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

Re: Issue with to_timestamp function

От

Melvin Davidson

Дата:

09 сентября 2014 г., 00:29:54

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 <LOquin@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.

Re: Issue with to_timestamp function

От

Jerry Sievers

Дата:

09 сентября 2014 г., 00:31:22

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 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
(MSDTC). 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
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
tryto 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
statementreturns 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

Re: Issue with to_timestamp function

От

Adrian Klaver

Дата:

09 сентября 2014 г., 00:47:35

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@aklaver.com

Re: Issue with to_timestamp function

От

Melvin Davidson

Дата:

09 сентября 2014 г., 02:34:56

>The data is

>ts

>08/06/2014 03:08:58

>08/06/2014 03:08:58>08/06/2014 03:08:58

Hmmm, this works for me:

CREATE TABLE sql_log_import
(
id serial NOT NULL,
ts text, -- will convert to ts when merging into sql_server_logs
CONSTRAINT sql_log_import_pk PRIMARY KEY (id)
)
WITH ( OIDS=FALSE );

INSERT INTO sql_log_import
VALUES
(1, '08/06/2014 03:08:58'),
(2, '08/06/2014 03:08:58'),
(3, '08/06/2014 03:08:58')

SELECT to_timestamp(ts, 'MM/DD/YYYY hh24:mi:ss')::timestamp FROM sql_log_import;

2014-08-06 03:08:58
2014-08-06 03:08:58
2014-08-06 03:08:58

Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Re: Issue with to_timestamp function

От

Adrian Klaver

Дата:

09 сентября 2014 г., 03:13:21

On 09/08/2014 04:06 PM, Lou Oquin wrote:
> I'm executing the query in pgAdmin3, in a SQL query window. The results are  coming from the history tab of the
outputpane. 

Alright. I was trying to clear up confusion on my end, because the log
entries you show are coming from SQL Server.

Do you have log data from Postgres that cover the errors?

Also you mention- 'Here’s a copy of the first few lines of the data
imported to table sql_log_import:', but show the SQL Server log. Could
we see that data?

What happens if you run the command from psql?

>
> Thanks
>
> Lou

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Issue with to_timestamp function

От

Adrian Klaver

Дата:

09 сентября 2014 г., 04:04:40

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.
>

>
> 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?

To get that error I had to do something like this:

hplc=> select to_timestamp('aug/06/2014 03:08:58 ', 'MM/DD/YYYY
hh24:mi:ss');
ERROR:  invalid value "au" for "MM"
DETAIL:  Value must be an integer.

So at a guess, the data being imported has some month abbreviations in it.

>
> Thanks
>
> *Lou O’Quin*
>

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Issue with to_timestamp function

От

Lou Oquin

Дата:

09 сентября 2014 г., 18:30:30

The data is

08/06/2014 03:08:58

Thanks

Lou

From: Melvin Davidson [mailto:melvin6925@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 <LOquin@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,Error: 17054<c/> Severity: 16<c/> State: 1.

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.

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.

Re: Issue with to_timestamp function

От

Lou Oquin

Дата:

09 сентября 2014 г., 18:30:34

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

Re: Issue with to_timestamp function

От

Lou Oquin

Дата:

09 сентября 2014 г., 18:30:44

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@aklaver.com]
Sent: Monday, September 08, 2014 2:47 PM
To: Lou Oquin; pgsql-general@postgresql.org
Subject: Re: [GENERAL] 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@aklaver.com

Re: Issue with to_timestamp function

От

Adrian Klaver

Дата:

09 сентября 2014 г., 19:39:22

On 09/08/2014 04:18 PM, Lou Oquin wrote:
> 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
>

So what do you get if you do:

select sli.ts
from public.sql_log_import sli
where sli.id <= 10;

> Thanks
>
> Lou



--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Issue with to_timestamp function

От

Adrian Klaver

Дата:

09 сентября 2014 г., 19:46:17

On 09/08/2014 04:18 PM, Lou Oquin wrote:
> 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"

Aah, just realized something. When you run the query I sent in my last
post I bet you will find the values in sli.ts where entered with double
quotes:

"08/06/2014 03:08:58"

So:

test=> create table ts_test (fld_1 text);
CREATE TABLE


test=> insert  into  ts_test values ('"08/06/2014 03:08:58"');
INSERT 0 1


test=> select * from ts_test ;


          fld_1


-----------------------


  "08/06/2014 03:08:58"


(1 row)





test=> select fld_1::timestamptz from ts_test ;


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
>



--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Issue with to_timestamp function

От

Lou Oquin

Дата:

10 сентября 2014 г., 15:18:33

Thanks for your help, Adrian.

Had a fire to put out before I left for home yesterday, and did not see the replies from you, Melvin Davidson  and
JerrySievers  until this morning.  I read the most recent (yours) first) and ran the query in psql; it complained about
UTF8encoding characters in the data.  Then dug into the raw data and found there were three hi-bit characters in front
ofthe '0' on the first record.  Replaced the first records date with the second records 'identical' (but without the
addedcharacters) and the timestamp casting now works as expected. 

Then, when I read Jerry's reply, saw that he had spotted it late yesterday afternoon.

 It is a sql server log file that I'm importing into my local database;  I'm using pg in analyzing the log data.

I apparently selected ascii  instead of UTF8 encoding when I imported the sql server log file with pgadmin...

Thanks again.

Lou
-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Monday, September 08, 2014 6:04 PM
To: Lou Oquin; pgsql-general@postgresql.org
Subject: Re: [GENERAL] 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.
>

>
> 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?

To get that error I had to do something like this:

hplc=> select to_timestamp('aug/06/2014 03:08:58 ', 'MM/DD/YYYY hh24:mi:ss');
ERROR:  invalid value "au" for "MM"
DETAIL:  Value must be an integer.

So at a guess, the data being imported has some month abbreviations in it.

>
> Thanks
>
> *Lou O'Quin*
>

--
Adrian Klaver
adrian.klaver@aklaver.com

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Обсуждение: Issue with to_timestamp function