Talend community forum / [resolved] Convert date and empty date

Scots · 2011-07-20 10:06:01

Hi,

I have a problem in a tMap.

I read a RSS feed with pubdate in GMT or CEST Time.

My colum table is a DATETIME type.

1. How to convert GMT to DATETIME ?
2. CEST is not read by tRSSInput. So, How to insert datetime.now if pubdate is empty ?

Thanks in advance.

Regards

Scots · 2011-07-20 12:46:13

Nobody can help me ?

Scots · 2011-07-20 13:34:23

For the empty date I tried this :
(row1.PUBDATE.isEmpty())
? TalendDate.parseDate("yyyy-MM-dd HH:mm:ss","TalendDate.getCurrentDate()")
:row1.PUBDATE

but don't working

Scots · 2011-07-20 14:02:38

I tried this :

(row1.PUBDATE.isEmpty())
? TalendDate.parseDate("yyyy-MM-dd HH:mm:ss","2011-07-30 12:51:12")
: new java.util.Date(Long.parseLong(row1.PUBDATE))

But when the job is on the first GMT date, the error is :

Exception in component tMap_1
java.lang.NumberFormatException: For input string: "20 Jul 2011 11:50:00 GMT"
at java.lang.NumberFormatException.forInputString(Unknown Source)
at java.lang.Long.parseLong(Unknown Source)
at java.lang.Long.parseLong(Unknown Source)
at mediascit.recupflux_0_1.RecupFlux.tMysqlInput_1Process(RecupFlux.java:1246)
at mediascit.recupflux_0_1.RecupFlux.runJobInTOS(RecupFlux.java:1708)
at mediascit.recupflux_0_1.RecupFlux.main(RecupFlux.java:1579)

And : How can I have the currentdate in place of "2011-07-30 12:51:12" ? getCurrentDate don't work

alevy · 2011-07-21 02:48:29

There are a number of problems with your expressions:

1. parseDate converts a String to a Date i.e. the second parameter of parseDate must be a date string as you have correctly used it in your last post. TalendDate.getCurrentDate(), however, already returns a Date so does not need to have parseDate applied to it.
2. In any case, since it is a function, getCurrentDate should not be enclosed in quotes otherwise you are trying to parse an actual String containing the words "TalendDate.getCurrentDate()".
3. If you actually wanted the current date as a String, you could use e.g. TalendDate.getDate("yyyy-MM-dd HH:mm:ss")
4. From the error, it appears that row1.PUBDATE contains the String "20 Jul 2011 11:50:00 GMT", which obviously cannot be parsed to a Long since it is not a whole number. You should simply be parsing it directly to a Date.

i.e.

row1.PUBDATE.isEmpty()
? TalendDate.getCurrentDate()
: TalendDate.parseDate("dd MMM yyyy HH:mm:ss zzz",row1.PUBDATE)

Scots · 2011-07-21 09:55:29

Hi,

I resolved my problem last night.

It's the good solution.

Thanks.

Scots · 2011-07-21 11:01:39

I discovered a bug :

My job working in my Studio but don't work in production after publication.

Error : Data truncation: Incorrect datetime value: '20 Jul 2011 23:00:00 GMT' for column 'date' at row 1
Error : Data truncation: Incorrect datetime value: '21 Jul 2011 08:37:35 GMT' for column 'date' at row 1
Error : Data truncation: Incorrect datetime value: '21 Jul 2011 07:18:00 GMT' for column 'date' at row 1

The database and tables are the same ...

Any idea ?

alevy · 2011-07-21 11:05:07

It looks like you're trying to pass the original row1.PUBDATE String to the database without parsing it to a Date.

Scots · 2011-07-22 11:51:08

(row1.PUBDATE.isEmpty())
? TalendDate.currentDate()
: TalendDate.parseDate("dd MMM yyyy HH:mm:ss",row1.PUBDATE.substring(0,row1.PUBDATE.indexOf("G")-1))

The currentDate() return a datetime or a string ???

My database column is DATETIME format.

Scots · 2011-08-25 16:29:34

Back from holidays ...

My function :

(row7.PUBDATE.isEmpty())
? TalendDate.currentDate()
: TalendDate.parseDate("dd MMM yyyy HH:mm:ss",row7.PUBDATE.substring(0,row7.PUBDATE.indexOf("G")-1))

Error message :
La méthode currentDate() est indéfinie pour le type TalendDate

Any idea ?

Thanks in advance

alevy · 2011-08-26 01:32:45

It should be TalendDate.getCurrentDate()

Scots · 2011-08-26 10:33:18

Thanks but there is a bug afer few lines :

Exception in component tMap_3
java.lang.RuntimeException: java.text.ParseException: Unparseable date: "26 Aug 2011 08:06:00"
at routines.TalendDate.parseDate(TalendDate.java:503)
at mediascit.recupflux_0_1.RecupFlux.tMysqlInput_6Process(RecupFlux.java:2148)
at mediascit.recupflux_0_1.RecupFlux.tMysqlInput_5Process(RecupFlux.java:1062)
at mediascit.recupflux_0_1.RecupFlux.runJobInTOS(RecupFlux.java:4332)
at mediascit.recupflux_0_1.RecupFlux.main(RecupFlux.java:4203)
Caused by: java.text.ParseException: Unparseable date: "26 Aug 2011 08:06:00"
at java.text.DateFormat.parse(Unknown Source)
at routines.TalendDate.parseDate(TalendDate.java:501)
... 4 more

alevy · 2011-08-27 07:25:30

If you're using TalendDate.parseDate("dd MMM yyyy HH:mm:ss","26 Aug 2011 08:06:00") you should not have a problem. Click on the Code tab at the bottom left of your job-design and find the exact line referred to in the error (2148 from above) to double-check the source of the problem.

Scots · 2011-08-27 11:45:58

Hi, sorry, don't understood all ...

I'm going to code tab :

if (outTMP.source == null) {
pstmt_tMysqlOutput_7.setNull(5,
java.sql.Types.VARCHAR);
} else {
pstmt_tMysqlOutput_7.setString(5,
outTMP.source);
}

2148 line is outTMP.source);

So ?

alevy · 2011-08-27 13:10:17

Well, that can't be right. Run the RecupFlux job to make sure you've got the correct line reference and then go to the Code tab again. What version are you using?

Scots · 2011-08-30 10:36:18

Hi, sorry to be late. Meetings ...

My Version : TIS 4.1.2.r53616

I runed the job again. Error (the same) :
[statistics] connecting to socket on port 3488
[statistics] connected
Exception in component tMap_3
java.lang.RuntimeException: java.text.ParseException: Unparseable date: "30 Aug 2011 07:06:00"
at routines.TalendDate.parseDate(TalendDate.java:503)
at mediascit.recupflux_0_1.RecupFlux.tMysqlInput_6Process(RecupFlux.java:2148)
at mediascit.recupflux_0_1.RecupFlux.tMysqlInput_5Process(RecupFlux.java:1062)
at mediascit.recupflux_0_1.RecupFlux.runJobInTOS(RecupFlux.java:4332)
at mediascit.recupflux_0_1.RecupFlux.main(RecupFlux.java:4203)
Caused by: java.text.ParseException: Unparseable date: "30 Aug 2011 07:06:00"
at java.text.DateFormat.parse(Unknown Source)
at routines.TalendDate.parseDate(TalendDate.java:501)
... 4 more
[statistics] disconnected
Job RecupFlux terminé à 10:33 30/08/2011. [Code sortie=1]

The code :
outTMP_tmp.date_insertion = (row7.PUBDATE
.isEmpty()) ? TalendDate
.getCurrentDate()
: TalendDate
.parseDate(
"dd MMM yyyy HH:mm:ss",
row7.PUBDATE
.substring(
0,
row7.PUBDATE
.indexOf("G") - 1));
outTMP_tmp.id_statut = null;
outTMP_tmp.note = null;
outTMP_tmp.etat = "0";
outTMP = outTMP_tmp;

The line 2148 is the ".parseDate(

shong · 2011-08-30 10:45:01

Hi Scots

Exception in component tMap_3
java.lang.RuntimeException: java.text.ParseException: Unparseable date: "30 Aug 2011 07:06:00"

It seems you do not come from a English speaking country, try to use the method
TalendDate.parseDateLocale("dd MMM yyyy HH:mm:ss", "30 Aug 2011 07:06:00","EN")

Best regards
Shong

Scots · 2011-08-30 11:25:00

Yes, I'm in France

Good job Shong, it works

I try to publish my job on my server.

Announcement

Unanswered posts

#1 2011-07-20 10:06:01

[resolved] Convert date and empty date

#2 2011-07-20 12:46:13

Re: [resolved] Convert date and empty date

#3 2011-07-20 13:34:23

Re: [resolved] Convert date and empty date

#4 2011-07-20 14:02:38

Re: [resolved] Convert date and empty date

#5 2011-07-21 02:48:29

Re: [resolved] Convert date and empty date

#6 2011-07-21 09:55:29

Re: [resolved] Convert date and empty date

#7 2011-07-21 11:01:39

Re: [resolved] Convert date and empty date

#8 2011-07-21 11:05:07

Re: [resolved] Convert date and empty date

#9 2011-07-22 11:51:08

Re: [resolved] Convert date and empty date

#10 2011-08-25 16:29:34

Re: [resolved] Convert date and empty date

#11 2011-08-26 01:32:45

Re: [resolved] Convert date and empty date

#12 2011-08-26 10:33:18

Re: [resolved] Convert date and empty date

#13 2011-08-27 07:25:30

Re: [resolved] Convert date and empty date

#14 2011-08-27 11:45:58

Re: [resolved] Convert date and empty date

#15 2011-08-27 13:10:17

Re: [resolved] Convert date and empty date

#16 2011-08-30 10:36:18

Re: [resolved] Convert date and empty date

#17 2011-08-30 10:45:01

Re: [resolved] Convert date and empty date

#18 2011-08-30 11:25:00

Re: [resolved] Convert date and empty date

Board footer