problem with joins on date fields using infobright tables

[_AnonymousUser]

I have designed a job using MyISAM engine tables in MySQL and did not have any problems with it. I have tried to implement the same job design using INFOBRIGHT engine in MySQL for the tables and job does not do the expected join.
Job design overview: Joining two tables on their date fields. The data type for both the date fields are timestamp but the date pattern in the tMap is specified as "yyyy-MM-dd" for both of them.
Error: does not return any result set for the join when infobright tables are used.
Do I have to do any additional transformations on the date field? It would be very helpful if somebody could guide me the right direction for this.

[Anonymous]

Hello
What's the date format before tMap? Can you try to compare Long value of the two date by Date.getTime();
Best regards

shong

[_AnonymousUser]

The date format before tMap for the fields from both the table are :
table A : yyyy-MM-dd hh:MM:ss
table B : yyyy-MM-dd 00:00:00
In the tMap I have specified the format as "yyyy-MM-dd" for both the fields ( did same when MYIsam tables were used and the join was performed correctly).
I did a long comparsion and they do not match since the minutes from the time part is also being considered. Below are the long values for both the fields for a date 2009-04-28.
table A: long value = 1380384033000 , date : 2009-04-28 09:57:33
table B: long value = 1240902000000 , date : 2009-04-28

[Anonymous]

A possible workaround for your problem could be to "reconvert" the problem date by making a call like this:
TalendDate.parseDate("yyyy-MM-dd","" + row1.table_a)

[Anonymous]

Hello Dimple
To make both MyISAM and INFOBRIGHT situation works correctly, convet Date to String and compare String record.
eg:
table A(date : 2009-04-28 09:57:33 )
table B(date : 2009-04-28 )
convert Date to String

TalendDate.formatDate("yyyy-MM-dd HH:mm:ss", tableA.date).substring(0,10)  //to get string date:2009-04-28

TalendDate.formatDate("yyyy-MM-dd", tableB.date)   //to get string date:2009-04-28

Then, compare string if they are identical.
Best regards

shong

[_AnonymousUser]

Hello,
Shong - if I do the above conversion then perform the join then it works. but this way I would have to use two additional tMap components in the job design ( one for each table).
If I use the conversion mentioned by John for the date field for table A and join it with the date field for table B then it works and in this way I do not have to use additional tMap components.
John/ Shong - could anyone of you please explain the function of ","" + row1.table_a portion in the code?

 TalendDate.parseDate("yyyy-MM-dd","" + row1.table_a).

Thanks,
Dimple

[Anonymous]

Hello Dimple

John/ Shong - could anyone of you please explain the function of ","" + row1.table_a portion in the code?

You can see the method defined by Talend,

    /**
     * Parses text from the beginning of the given string to produce a date using the given pattern and the default date
     * format symbols for the given locale. The method may not use the entire text of the given string.
     * <p>
     * 
     * @param pattern the pattern to parse.
     * @param stringDate A <code>String</code> whose beginning should be parsed.
     * @return A <code>Date</code> parsed from the string.
     * @throws ParseException
     * @exception ParseException if the beginning of the specified string cannot be parsed.
     * 
     * {talendTypes} Date
     * 
     * {Category} TalendDate
     * 
     * {param} string("yyyy-MM-dd HH:mm:ss") pattern : the pattern to parse
     * 
     * {param} string("") stringDate : A <code>String</code> whose beginning should be parsed
     * 
     * {example} parseDate("yyyy-MMM-dd HH:mm:ss", "23-Mar-1979 23:59:59") #
     */
    public synchronized static Date parseDate(String pattern, String stringDate) {
        try {
            return FastDateParser.getInstance(pattern).parse(stringDate);
        } catch (ParseException e) {
            throw new RuntimeException(e);
        }
    }

For example:
tMysqlInput--row1-->tMap-->tLogRow
You will parse a String to a Date on tMap:
TalendDate.parseDate("yyyy-MM-dd", row1.table_a). //table_a is column name.
Best regards
shong

[Anonymous]

Hi Dimple,
TalendDate.formatDate takes (String,Date). The TalendDate.parseDate function takes parameters of type (String,String). The first parameter is the format string, and then the date string to be formatted. Since you are using a Date type object it is neccessary to convert it to a String type to use this function. This can be done with the toString() function, or as John showed, Java will implicty cast a another data type to a String when used with the concatenate operator "+", so by concatenating the Date to an empty string ("" + ) the date is converted to String for us. The reason you experience this issue is because your are using a DATETIME or TIMESTAMP field, instead of a DATE field. Although you can display the date in any format that you wish with the date pattern, Java knows the actual complete DATETIME of the Date object (including the HH:mm:ss, which is why you noticed the Long times do not match. ) and the values would need to be the same down to the second in order to join correctly as this is what the actual values are. An alternative to what has already been suggested is to convert the value from DATETIME/TIMESTAMP to DATE when retrieving the value from MySQL by casting it in the select statement in your MySQLInput, with the following: "select CAST(datetimeColumn AS DATE) from TableName;"
http://dev.mysql.com/doc/refman/5.1/en/datetime.html

Thanks!
Josh

[_AnonymousUser]

Thank you everybody for all the detailed explanation regarding the problem.