Issue with to_timestamp function

Discussion:

Lou Oquin

2014-09-08 20:52:05 UTC

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

2014-09-08 21:29:48 UTC

Permalink

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;

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

Melvin Davidson

2014-09-08 23:34:47 UTC

Permalink

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;

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

Lou Oquin

2014-09-08 23:03:32 UTC

Permalink

The data is

ts
ï»¿08/06/2014 03:08:58
08/06/2014 03:08:58
08/06/2014 03:08:58

Thanks

Lou
From: Melvin Davidson [mailto:***@gmail.com]
Sent: Monday, September 08, 2014 2:30 PM
To: Lou Oquin
Cc: pgsql-***@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 <***@nammotalley.com<mailto:***@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. [Loading Image...

]

Jerry Sievers

2014-09-08 21:31:01 UTC

Permalink

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.
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';
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
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.
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: ***@comcast.net
p: 312.241.7800

--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Lou Oquin

2014-09-08 23:18:41 UTC

Permalink

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:***@comcast.net]
Sent: Monday, September 08, 2014 2:31 PM
To: Lou Oquin
Cc: pgsql-***@postgresql.org
Subject: Re: [GENERAL] Issue with to_timestamp function

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.
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';
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
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.
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: ***@comcast.net
p: 312.241.7800

--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgr

Adrian Klaver

2014-09-09 16:39:14 UTC

Permalink

Post by Lou Oquin
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;
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;

Post by Lou Oquin
Thanks
Lou

--
Adrian Klaver
***@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Adrian Klaver

2014-09-09 16:46:04 UTC

Permalink

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

Post by Lou Oquin
********** Error **********
ERROR: invalid input syntax for type timestamp with time zone: "08/06/2014 03:08:58"
SQL state: 22007
Thanks
Lou

--
Adrian Klaver
***@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Adrian Klaver

2014-09-08 21:47:27 UTC

Permalink

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.
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
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
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.
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
***@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Adrian Klaver

2014-09-09 00:13:08 UTC

Permalink

I'm executing the query in pgAdmin3, in a SQL query window. The results are coming from the history tab of the output pane.

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
***@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Lou Oquin

2014-09-08 23:06:21 UTC

Permalink

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

Post by Lou Oquin
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.
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';
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
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.
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?

Post by Lou Oquin
Thanks
*Lou O'Quin*

--
Adrian Klaver
***@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Adrian Klaver

2014-09-09 01:04:25 UTC

Permalink

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
***@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Lou Oquin

2014-09-09 17:07:37 UTC

Permalink

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 Jerry Sievers until this morning. I read the most recent (yours) first) and ran the query in psql; it complained about UTF8 encoding characters in the data. Then dug into the raw data and found there were three hi-bit characters in front of the '0' on the first record. Replaced the first records date with the second records 'identical' (but without the added characters) 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:***@aklaver.com]
Sent: Monday, September 08, 2014 6:04 PM
To: Lou Oquin; pgsql-***@postgresql.org
Subject: Re: [GENERAL] Issue with to_timestamp function

Post by Lou Oquin
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.

Post by Lou Oquin
Thanks
*Lou O'Quin*

--
Adrian Klaver
***@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general