topic Re: Subtract specific time interval from a given date in Talend Studio

Subtract specific time interval from a given date

Anonymous — Sat, 16 Nov 2024 13:19:00 GMT

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.

Re: Subtract specific time interval from a given date

Anonymous — Thu, 12 Aug 2010 00:05:13 GMT

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;

Re: Subtract specific time interval from a given date

Anonymous — Thu, 12 Aug 2010 00:20:20 GMT

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

Re: Subtract specific time interval from a given date

Anonymous — Wed, 08 Sep 2010 19:17:42 GMT

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.

Re: Subtract specific time interval from a given date

Anonymous — Mon, 25 Nov 2013 08:44:47 GMT

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

Re: Subtract specific time interval from a given date

Anonymous — Mon, 25 Nov 2013 13:20:22 GMT

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();
    } 
}

Re: Subtract specific time interval from a given date

Anonymous — Tue, 26 Nov 2013 10:56:48 GMT

Thanks for the reply....