Subtract specific time interval from a given date

[Anonymous]

Hello,
I have a table with Datetime column 'Week' (end of week date)
I need to subtract a day from this date.
In Teradata it works like this
select currentdate()-1 gives me yesterday date
In MySQL I would do this:
select DATE_SUB(now(), interval 1 day)
Now I want to do it in Talend without going on developign whole new application in Java to calcualte what I need. Is it possible in Talend?
I have tMySQLInput -> tMap -> tMySQLOutput components and hope to be able to do it in tMap.
Are there components which would allow me to pass calcualtions to database?
Thanks a lot for help!
Pit.

[Anonymous]

Hi,
To do that very simple, let me specify the different step :
1/ Expand the Code (from your Talend repository); then right-click on Routines and choose "Create Routine"; name it myRoutines and Finish.
2/ When the Routine Editor is Open; move after the Hello Example method (after line 52); add an empty line.
3/ Then copy the following method expression code which does your expected function :

private static Calendar cal = Calendar.getInstance();
/**
* getDateMinusADay: return the dateToChange substract ONE DAY (consistancy maintain for FEBRUARY or YEAR Change etc...)
* {talendTypes} Date
* {Category} User Defined
* {param} Date("dateToChange") dateToTest from the source
* {example} getDateMinusADay(dateToChange)
*/
public static Date getDateMinusADay(Date dateToChange) {
cal.setTime(dateToChange);
cal.add(cal.DATE, -1);
return cal.getTime();
}

4/ Save your routine, then open your Job, go back to your tMap, choose the button to open the expression editor into the COLUMN you want to tranform and then choose the User Defined function "getDateMinusADay" and fill the right argument.
5/ Save your tMap and Job and execute it : it works !!
Best regards;

[Anonymous]

In perl, you can use the Date::Manip module directly in a tmap:
Date::Manip: ateCalc($row1,"+ 6 hours")

[Anonymous]

Hello,
I tried to implement suggestion with java code. However the code doesn't compile. Please point out what I've done wrong.
// ============================================================================
//
// Copyright (C) 2006-2010 Talend Inc. - www.talend.com
//
// This source code is available under agreement available at
// %InstallDIR%\features\org.talend.rcp.branding.%PRODUCTNAME%\%PRODUCTNAME%license.txt
//
// You should have received a copy of the agreement
// along with this program; if not, write to Talend SA
// 9 rue Pages 92150 Suresnes, France
//
// ============================================================================
package routines;
/*
* user specification: the function's comment should contain keys as follows: 1. write about the function's comment.but
* it must be before the "{talendTypes}" key.
*
* 2. {talendTypes} 's value must be talend Type, it is required . its value should be one of: String, char | Character,
* long | Long, int | Integer, boolean | Boolean, byte | Byte, Date, double | Double, float | Float, Object, short |
* Short
*
* 3. {Category} define a category for the Function. it is required. its value is user-defined .
*
* 4. {param} 's format is: {param} <type> <name>
*
* <type> 's value should be one of: string, int, list, double, object, boolean, long, char, date. <name>'s value is the
* Function's parameter name. the {param} is optional. so if you the Function without the parameters. the {param} don't
* added. you can have many parameters for the Function.
*
* 5. {example} gives a example for the Function. it is optional.
*/
public class PreviousDate {
/**
* helloExample: not return value, only print "hello" + message.
*
*
* {talendTypes} String
*
* {Category} User Defined
*
* {param} string("world") input: The string need to be printed.
*
* {example} helloExemple("world") # hello world !.
*/
public static void helloExample(String message) {
if (message == null) {
message = "World"; //$NON-NLS-1$
}
System.out.println("Hello " + message + " !"); //$NON-NLS-1$ //$NON-NLS-2$
}
private static Calendar cal = Calendar.getInstance();
/**
* getDateMinusADay: return the dateToChange substract ONE DAY (consistancy maintain for FEBRUARY or YEAR Change etc...)
* {talendTypes} Date
* {Category} User Defined
* {param} Date("dateToChange") dateToTest from the source
* {example} getDateMinusADay(dateToChange)
*/
public static Date getDateMinusADay(Date dateToChange) {
cal.setTime(dateToChange);
cal.add(cal.DATE, -1);
return cal.getTime();
}
}

I am getting ERROR "Calendar cannot be resolved to a type" and "Date cannot be resolved to a type"
Thank you,
Peter.

[Anonymous]

what is the solution for the above error.i am getting the same error.please do the needful

[Anonymous]

Hi,
From what I see in your Code, you miss the import part of the JAVA Program.
import java.util.Calendar;
import java.util.Date;
These 2 Classes are missing.
Below is the CODE you should COPY/PASTE in your Routine.
(Be sure you name your Routine PreviousDate when you create it)

package routines;
import java.util.Calendar;
import java.util.Date;

/*
* user specification: the function's comment should contain keys as follows: 1. write about the function's comment.but
* it must be before the "{talendTypes}" key.
*
* 2. {talendTypes} 's value must be talend Type, it is required . its value should be one of: String, char | Character,
* long | Long, int | Integer, boolean | Boolean, byte | Byte, Date, double | Double, float | Float, Object, short |
* Short
*
* 3. {Category} define a category for the Function. it is required. its value is user-defined .
*
* 4. {param} 's format is: {param} <type> <name>
*
* <type> 's value should be one of: string, int, list, double, object, boolean, long, char, date. <name>'s value is the
* Function's parameter name. the {param} is optional. so if you the Function without the parameters. the {param} don't
* added. you can have many parameters for the Function.
*
* 5. {example} gives a example for the Function. it is optional.
*/
public class PreviousDate {
    /**
     * helloExample: not return value, only print "hello" + message.
     *
     *
     * {talendTypes} String
     *
     * {Category} User Defined
     *
     * {param} string("world") input: The string need to be printed.
     *
     * {example} helloExemple("world") # hello world !.
     */
    public static void helloExample(String message) {
        if (message == null) {
            message = "World"; //$NON-NLS-1$
        }
        System.out.println("Hello " + message + " !"); //$NON-NLS-1$ //$NON-NLS-2$
    }
    private static Calendar cal = Calendar.getInstance();
    /**
     * getDateMinusADay: return the dateToChange substract ONE DAY (consistancy maintain for FEBRUARY or YEAR Change etc...)
     * {talendTypes} Date
    * {Category} User Defined
     * {param} Date("dateToChange") dateToTest from the source
     * {example} getDateMinusADay(dateToChange)
     */
    public static Date getDateMinusADay(Date dateToChange) {
        cal.setTime(dateToChange);
        cal.add(cal.DATE, -1);
        return cal.getTime();
    } 
}

[Anonymous]

Thanks for the reply....