topic Re: problem with date format and tPostgresqlInput in Talend Studio

problem with date format and tPostgresqlInput

_AnonymousUser — Sat, 16 Nov 2024 14:23:09 GMT

Hi,
I have a problem with date format and tPostgresqlInput. In fact, I read some date in a Postgresql table and I have this error :
Exception in component tPostgresqlInput_1
org.postgresql.util.PSQLException: Mauvaise valeur pour le type timestamp : [C@32fb4f
at org.postgresql.jdbc2.TimestampUtils.loadCalendar(TimestampUtils.java:232)
at org.postgresql.jdbc2.TimestampUtils.toTimestamp(TimestampUtils.java:307)
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getTimestamp(AbstractJdbc2ResultSet.java:419)
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getTimestamp(AbstractJdbc2ResultSet.java:2088)
at test_compare_clover.test3.test3.tPostgresqlInput_1Process(test3.java:672)
at test_compare_clover.test3.test3.runJobInTOS(test3.java:1617)
at test_compare_clover.test3.test3.main(test3.java:1494)
Caused by: java.lang.NumberFormatException: Trailing junk on timestamp: ''
at org.postgresql.jdbc2.TimestampUtils.loadCalendar(TimestampUtils.java:226)
... 6 more

In Postgresql, it seems than date before Marh 1911 have this format : 1844-12-03 12:14:51 + 00:09:21 and the component tPostgresqlInput of TOS doesn't read this format of date.
So I think there is a bug in the component tPostgresqlInput about timestamp type.
Caro

Re: problem with date format and tPostgresqlInput

Anonymous — Tue, 06 May 2008 07:56:47 GMT

Hello

In Postgresql, it seems than date before Marh 1911 have this format : 1844-12-03 12:14:51 + 00:09:21 and the component tPostgresqlInput of TOS doesn't read this format of date.

How do you set the date pattern for this format? Can you show more detailed information?
Best regards
shong

Re: problem with date format and tPostgresqlInput

_AnonymousUser — Tue, 06 May 2008 09:13:52 GMT

Excuse me, I don't know how insert pictures in a reply so I can't show you my job !
Could you explain me, please ?
Caro

Re: problem with date format and tPostgresqlInput

Anonymous — Tue, 06 May 2008 09:19:45 GMT

Hello

Excuse me, I don't know how insert pictures in a reply so I can't show you my job !
Could you explain me, please ?

Before you upload a screenshot, you must register a member account. Capture your screenhost on your local machine, then upload it. See this 831.
Best regards
shong

Re: problem with date format and tPostgresqlInput

Anonymous — Tue, 06 May 2008 09:47:58 GMT

Thanks for your answer.
I realised a simple test : read data with tPostegresqlInput and write it in a delimited file.
The second picture is an exemple of my read data. I have 2 fields : beginning date and ending date (Timestamp format)
Then, there is a picture representing my job and the error message.
Postgresql uses these 2 formats because there was a law in March 1911 in France to use universal time. Before that, used time was Paris time + 9 minutes and 21 seconds (see the first picture). So I don't know if we use Postegresql in another country, these formats are same!
After, I tried to read data with "where bgdate> '01/04/1911' and enddate > '01/04/1911' " and the job works fine. So I think the component tPosgresqlInput is not prepared to the format "00:00:00 + 00:09:21" but works fine with "00:00:00 +01".
Caro

Re: problem with date format and tPostgresqlInput

Anonymous — Wed, 07 May 2008 10:58:04 GMT

I tried to read my data with a tJDBCInput and it works. So it is not a solution for the component tPostgresqlIput but a bypass !
Caro