topic Re: Date and Time Format With HH24 in Talend Studio

Date and Time Format With HH24

Anonymous — Wed, 30 May 2012 08:08:59 GMT

Hi
I have input Date coming with "18/APR/91 16:00:00" from oracle , I need to load the into Postgresql table
YEAR
MONTH
DATE
HOUR
each have separately column, and I'm using following method.

TalendDate.getPartOfDate("YEAR",row1.PSTART)
TalendDate.getPartOfDate("MONTH",row1.PSTART)
TalendDate.getPartOfDate("DAY_OF_MONTH",row1.PSTART)
TalendDate.getPartOfDate("HOUR",row1.PSTART)
Im getting following result
YEAR = 1991
MONTH = 3
DATE = 18
HOUR = 4
but we expecting result should be
YEAR = 1991
MONTH = 4
DATE = 18
HOUR = 16
Please let me know..
Thanks
Rajesh G

Re: Date and Time Format With HH24

Anonymous — Wed, 30 May 2012 08:27:38 GMT

Hi Rajesh
The TalendDate.getPartOfDate() method calls java.util.Calendar to get return value.
For MONTH, Calendar class defines the return value of January is 0 while April is 3... You have to add 1 manually.
For HOUR, use 16%12.
Regards,
Pedro

Re: Date and Time Format With HH24

Anonymous — Wed, 30 May 2012 08:45:02 GMT

Thanks for Input..
Your mean to say I have use like this? What format can I use?

TalendDate.getPartOfDate("HOUR",row1.PSTART) %12

Please let me know?
Thanks
Rajesh G

Re: Date and Time Format With HH24

Anonymous — Wed, 30 May 2012 08:46:43 GMT

Hi
Yes. The expression is correct.
Regards,
Pedro

Re: Date and Time Format With HH24

Anonymous — Wed, 30 May 2012 08:51:43 GMT

Thanks..but Still I'm getting same result..
instead of 16 again Im getting HOUR = 4
please let me know any other format?
Thanks
Rajesh G

Re: Date and Time Format With HH24

Anonymous — Wed, 30 May 2012 09:02:03 GMT

Hi Rajesh
Sorry. My fault...
You'd better modify TalendDate.getPartOfDate() method as seen bellow.

  public static int getPartOfDate(String partName, Date date) {
        if (partName == null || date == null)
            return 0;
        int ret = 0;
        String[] fieldsName = { "YEAR", "MONTH", "HOUR", "MINUTE", "SECOND", "DAY_OF_WEEK", "DAY_OF_MONTH", "DAY_OF_YEAR",
                "WEEK_OF_MONTH", "DAY_OF_WEEK_IN_MONTH", "WEEK_OF_YEAR", "TIMEZONE", "HOUR OF DAY" };
        java.util.List<String> filedsList = java.util.Arrays.asList(fieldsName);
        Calendar c = Calendar.getInstance();
        c.setTime(date);
        switch (filedsList.indexOf(partName)) {
        case 0:
            ret = c.get(Calendar.YEAR);
            break;
        case 1:
            ret = c.get(Calendar.MONTH);
            break;
        case 2:
            ret = c.get(Calendar.HOUR);
            break;
        case 3:
            ret = c.get(Calendar.MINUTE);
            break;
        case 4:
            ret = c.get(Calendar.SECOND);
            break;
        case 5:
            ret = c.get(Calendar.DAY_OF_WEEK);
            break;
        case 6:
            ret = c.get(Calendar.DAY_OF_MONTH);
            break;
        case 7:
            ret = c.get(Calendar.DAY_OF_YEAR);
            break;
        case 8:
            // the ordinal number of current week in a month (it means a 'week' may be not contain 7 days)
            ret = c.get(Calendar.WEEK_OF_MONTH);
            break;
        case 9:
            // 1-7 correspond to 1, 8-14 correspond to 2,...
            ret = c.get(Calendar.DAY_OF_WEEK_IN_MONTH);
            break;
        case 10:
            ret = c.get(Calendar.WEEK_OF_YEAR);
            break;
        case 11:
            ret = (c.get(Calendar.ZONE_OFFSET)) / (1000 * 60 * 60);
            break;
        case 12:
        	ret = c.get(Calendar.HOUR_OF_DAY);
            break;
        default:
            break;
        }
        return ret;
    }

Then you can use expression TalendDate.getPartOfDate("HOUR OF DAY",row1.PSTART).
The return value is 16 this time.
Regards,
Pedro

Re: Date and Time Format With HH24

Anonymous — Wed, 30 May 2012 09:17:24 GMT

Thanks for your help its very helpful....
for TalendDate.getPartOfDate() this is SYSTEM Routine. How should edit and modify?
or Can I create my own Routine using same code?
Thanks
Rajesh G

Re: Date and Time Format With HH24

Anonymous — Wed, 30 May 2012 09:19:26 GMT

Hi Rajesh
Yes. You can create a custom routine using the code above.
Regards,
Pedro

Re: Date and Time Format With HH24

Anonymous — Thu, 31 May 2012 02:00:44 GMT

Thanks Pedro.
after creating custom routine and using DateFunction.getPartOfDate("HOUR_OF_DAY",row1.PSTART)
Im getting value instead of 16 HOUR = 0
If i use following method
DateFunction.getPartOfDate("HOUR_OF_DAY",row1.PSTART) = 0
DateFunction.getPartOfDate("HOUR",row1.PSTART) = 4
Im not sure the problem ?
Please let me know..

Re: Date and Time Format With HH24

Anonymous — Thu, 31 May 2012 03:05:31 GMT

Hi
I'm afraid you didn't create this custom routine correctly.
Don't forget to import these classes on the top of your routine.
import java.util.Calendar;
import java.util.Date;
And I have written an email and attached my custom routine.
Regards,
Pedro

Re: Date and Time Format With HH24

Anonymous — Thu, 31 May 2012 03:26:21 GMT

Thanks for that....
I did import all java util as well.. Please find code here.. after that also still its not working...
I'm unable import custom routine into my Talend Job which you sent thru email...
I did following method to import the your custom routine
Correct me If worng:-
Code > Routines > Right-click Routines and selected Import items > Select archive file path >

Please let me know..

// ============================================================================
//
// Copyright (c) 2005-2011, Talend Inc.
//
// This source code has been automatically generated by_Talend Open Studio for Data Integration
// CodeGenerator version 5.0.1.r74687
// You can find more information about Talend products at www.talend.com.
// You may distribute this code under the terms of the GNU LGPL license
// http://www.gnu.org/licenses/lgpl.html).
//
// ============================================================================
package routines;

import java.util.Calendar;
import java.util.Date;

public class DateFunction {
/**
* 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 int getPartOfDate(String partName, Date date) {
if (partName == null || date == null)
return 0;
int ret = 0;
String[] fieldsName = { "YEAR", "MONTH", "HOUR", "MINUTE", "SECOND", "DAY_OF_WEEK", "DAY_OF_MONTH", "DAY_OF_YEAR",
"WEEK_OF_MONTH", "DAY_OF_WEEK_IN_MONTH", "WEEK_OF_YEAR", "TIMEZONE", "HOUR OF DAY" };
java.util.List<String> filedsList = java.util.Arrays.asList(fieldsName);
Calendar c = Calendar.getInstance();
c.setTime(date);
switch (filedsList.indexOf(partName)) {
case 0:
ret = c.get(Calendar.YEAR);
break;
case 1:
ret = c.get(Calendar.MONTH);
break;
case 2:
ret = c.get(Calendar.HOUR);
break;
case 3:
ret = c.get(Calendar.MINUTE);
break;
case 4:
ret = c.get(Calendar.SECOND);
break;
case 5:
ret = c.get(Calendar.DAY_OF_WEEK);
break;
case 6:
ret = c.get(Calendar.DAY_OF_MONTH);
break;
case 7:
ret = c.get(Calendar.DAY_OF_YEAR);
break;
case 8:
// the ordinal number of current week in a month (it means a 'week' may be not contain 7 days)
ret = c.get(Calendar.WEEK_OF_MONTH);
break;
case 9:
// 1-7 correspond to 1, 8-14 correspond to 2,...
ret = c.get(Calendar.DAY_OF_WEEK_IN_MONTH);
break;
case 10:
ret = c.get(Calendar.WEEK_OF_YEAR);
break;
case 11:
ret = (c.get(Calendar.ZONE_OFFSET)) / (1000 * 60 * 60);
break;
case 12:
ret = c.get(Calendar.HOUR_OF_DAY);
break;
default:
break;
}
return ret;
}
}

Re: Date and Time Format With HH24

Anonymous — Thu, 31 May 2012 03:29:13 GMT

Hi
Finally, I know the root reason.
It should be DateFunction.getPartOfDate("HOUR OF DAY",row1.PSTART).
What you typed is DateFunction.getPartOfDate("HOUR_OF_DAY",row1.PSTART).
Regards,
Pedro

Re: Date and Time Format With HH24

Anonymous — Thu, 31 May 2012 04:28:03 GMT

Sorry for troubling you..again Im getting same issue.
Im getting value instead of 16 HOUR = 0
Now I have used DateFunction.getPartOfDate("HOUR OF DAY",row1.PSTART) even same issue.
I checked the internet as well for "java.util.calendar" example
they are using "Calendar.HOUR_OF_DAY", same thing when i was using I'm not getting correct result.
Please find the link here..
http://www.roseindia.net/java/beginners/calendarexample.shtml
http://www.leepoint.net/notes-java/other/10time/30calendar.html
http://www.idevelopment.info/data/Programming/java/date/CalendarExample.java
http://www.leepoint.net/notes-java/other/10time/30calendar.html
Please let me know..
Thanks
Rajesh G

Re: Date and Time Format With HH24

Anonymous — Thu, 31 May 2012 04:40:43 GMT

Hi Rajesh
Sorry. The expression is correct. The custom routine is correct. I think I must miss some detail.
You'd better check it carefully.
Or send me an email with this export job and custom routine.
I will test it for you.
Regards,
Pedro

Re: Date and Time Format With HH24

Anonymous — Thu, 31 May 2012 05:27:20 GMT

Thanks Pedro..
I have send you the export ETL job and custom routine to your email.
Please have a look and let me know..
Thanks
Rajesh G

Re: Date and Time Format With HH24

Anonymous — Thu, 31 May 2012 05:35:31 GMT

Hi Rajesh
This only error I find is "HOUR OF DAY" as the following image.
Regards,
Pedro

Re: Date and Time Format With HH24

Anonymous — Thu, 31 May 2012 08:14:15 GMT

Thanks...Sorry again...
In custom routine i have declared like this..
case 12:
ret = c.get(Calendar.HOUR_OF_DAY);
break;
then, If use DateFunction.getPartOfDate("HOUR OF DAY",row1.PSTART) either this or that
OR
DateFunction.getPartOfDate("HOUR_OF_DAY",row1.PSTART)
Im getting same issue.
Im getting value instead of 16 HOUR = 0
Thanks
Rajesh G

Re: Date and Time Format With HH24

Anonymous — Thu, 31 May 2012 08:43:19 GMT

Hi
"HOUR OF DAY" or "HOUR_OF_DAY" is decided by the code below.

String[] fieldsName = { "YEAR", "MONTH", "HOUR", "MINUTE", "SECOND", "DAY_OF_WEEK", "DAY_OF_MONTH", "DAY_OF_YEAR",
                "WEEK_OF_MONTH", "DAY_OF_WEEK_IN_MONTH", "WEEK_OF_YEAR", "TIMEZONE", "HOUR OF DAY" };

You will see the last element of this String array.
Sorry. I can't find out why it doesn't work...
I am really eager to fly to your side...
Regards,
Pedro

Re: Date and Time Format With HH24

Anonymous — Fri, 01 Jun 2012 01:59:04 GMT

Thanks for your kind inputs and reply's...
Its working fine now...I did mistake when declare the strings
String[] fieldsName = { "YEAR", "MONTH", "HOUR", "MINUTE", "SECOND", "DAY_OF_WEEK", "DAY_OF_MONTH", "DAY_OF_YEAR", "WEEK_OF_MONTH", "DAY_OF_WEEK_IN_MONTH", "WEEK_OF_YEAR", "TIMEZONE", "HOUR_OF_DAY" };
We should declare "HOUR_OF_DAY" , but i was declaring "HOUR OF DAY" ... that y the problem...
Thanks very much..
Thanks
Rajesh G

Re: Date and Time Format With HH24

Anonymous — Fri, 01 Jun 2012 03:25:03 GMT

Hi Rajesh
Great!
Feel free to ask any questions about Talend here.
Regards,
Pedro