problem with date format and tPostgresqlInput

One Star

problem with date format and tPostgresqlInput

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
Community Manager

Re: problem with date format and tPostgresqlInput

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: problem with date format and tPostgresqlInput

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

Re: problem with date format and tPostgresqlInput

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: problem with date format and tPostgresqlInput

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
One Star

Re: problem with date format and tPostgresqlInput

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