convert time

[Anonymous]

Hi
I am having time of the date in the format 0.39886574074074 which actually is represent of the 9:34:22 time .
Does anyone know how I can convert number 0.39886574074074 to be this time format using talend .
Thanks
Zeljka

[Anonymous]

This is a copy in Java of JohnGarrettMartin code :
public static String myFunction(double excelTime)
{
int secInDay = 86400;
int minInDay = 1440;
int hourInDay = 24;

int myHour = new Double(excelTime * hourInDay).intValue();
int myMin = new Double((excelTime * minInDay) - (myHour * 60)).intValue();
int mySec = new Double((excelTime * secInDay) - ((myHour * 3600) + (myMin * 60))).intValue();

return myHour + ":" + myMin + ":" + mySec;
}

[Anonymous]

You can put these function in a routine class, and next call it from your Talend job.

[Anonymous]

Thanks

[Anonymous]

Hi,
This routine word as I expected but the only problem I am getting the time as 10:9:18 .. Is any way I can get everywhere as two digits 10:09:18 or 09:10:18
Thanks

[Anonymous]

Maybe if you try these code :
public static String myFunction(double excelTime)
{
int secInDay = 86400;
int minInDay = 1440;
int hourInDay = 24;

String myHour = Integer.toString(new Double(excelTime * hourInDay).intValue());
if(myHour.length() == 1)
{
myHour = "0" + myHour;
}

String myMin = Integer.toString(new Double((excelTime * minInDay) - (myHour * 60)).intValue());
if(myMin.length() == 1)
{
myMin = "0" + myMin;
}

String mySec = Integer.toString(new Double((excelTime * secInDay) - ((myHour * 3600) + (myMin * 60))).intValue());
if(mySec.length() == 1)
{
mySec = "0" + mySec;
}

return myHour + ":" + myMin + ":" + mySec;
}

Benoît.

[Anonymous]

Thanks ...
I have also try this one and it's working ...
package routines;
public class DateTime {
public static String myFunction(double InputTime)
{
int secInDay = 86400;
int minInDay = 1440;
int hourInDay = 24;
String myHourStringRep = "";
String myMinInDayStringRep = "";
String secInDayStringRep = "";

int myHour = new Double(InputTime * hourInDay).intValue();
int myMin = new Double((InputTime * minInDay) - (myHour * 60)).intValue();
int mySec = new Double((InputTime * secInDay) - ((myHour * 3600) + (myMin * 60))).intValue();

if(myHour < 10) {
myHourStringRep = "0" + myHour;
}else {
myHourStringRep = "" + myHour;
}

if(myMin < 10) {
myMinInDayStringRep = "0" + myMin;
}else {
myMinInDayStringRep = "" + myMin;
}

if(mySec < 10) {
secInDayStringRep = "0" + mySec;
}else {
secInDayStringRep = "" + mySec;
}

return myHourStringRep + ":" + myMinInDayStringRep + ":" + secInDayStringRep;
}
}

[Anonymous]

Thanks for help to convert number format from excel to normal time .
however I am facing new problem where I am having date and time as number format from excel (40682.55946
) and needs to convert to be date and time look like e.g. 2011-06-09T09:06:21
I am able to convert time but not sure how to add now date or convert number above as date and time and to input "T" between as my xml schema in database only accepting format as I stated 2011-06-09T09:06:21.
I have to stated that I am database developer not java , tried to find some solution on internet but not really with luck..
Thanks
Zeljka

[Anonymous]

Excel Dates are serial values based on 1 Jan 1900.
so the easy way to convert from the serial value to a "real" date is to simply add days to the date:
so in a tJavaRow:

String dt = "1900-01-01";  // Start date
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Calendar c = Calendar.getInstance();
c.setTime(sdf.parse(dt));
c.add(Calendar.DATE, input_row.EXCEL_DATE.intValue() );  // number of days to add
output_row.REAL_DATE= sdf.format(c.getTime());

edit: syntax error!

[Anonymous]

Sorry John but this is not working for me as my data type for this input row is string as I am converting time to be in normal format but I have notice that I am having in excel now number 40682.55946
which probably is date and time in one .
My question will be is any way that I can update my existiong routine which I am using to convet number to time , I now update this routines and convet completed number 40682.55946
to be date and time .
Thanks

[vladika]

?eljka? Each time you have a problem you wake up the whole cavalry
Let me just add a bit of a make up on Mr. GarretMartin's idea, who basically solved the problem.
First make sure you add this at the beginning of the routine:

import java.text.SimpleDateFormat;
import java.util.Calendar;

Now add new function:

 /**
	 * Convert ExcelDateTime Jan1900 serial date values in the way ZeljkaMom Wants
	 * 
	 * 
	 * {talendTypes} String
	 * 
	 * {Category} User Defined
	 * 
	 * {param} double(40682.55946)
	 * 
	 * {example} DateTime(40792.3794097222) # 2011-06-09T09:06:21
     * @throws ParseException 
	 */
	public static String DateTime(Double DateTimeXLS) throws ParseException {
		// Hardcoded start date - since Excel serial values based on 01.01.1900 are assumed
		String dt = "1899-12-30 00:00:00";
		
		int secInDay = 86400;
        int minInDay = 1440;
        int hourInDay = 24;
        
        Double InputTime = DateTimeXLS - DateTimeXLS.intValue();
        
        int myHour = new Double(InputTime * hourInDay).intValue();
        int myMin  = new Double((InputTime * minInDay) - (myHour * 60)).intValue();
        int mySec  = new Double((InputTime * secInDay) - ((myHour * 3600) + (myMin * 60))).intValue();
        
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		SimpleDateFormat sdf_date = new SimpleDateFormat("yyyy-MM-dd");
		SimpleDateFormat sdf_time = new SimpleDateFormat("HH:mm:ss");
		
		Calendar c = Calendar.getInstance();
		c.setTime(sdf.parse(dt));
		
		// number of days/hours/minutes/seconds to add
		c.add(Calendar.DATE, DateTimeXLS.intValue());
		c.add(Calendar.HOUR, myHour);
		c.add(Calendar.MINUTE, myMin);
		c.add(Calendar.SECOND, mySec);
		
		return sdf_date.format(c.getTime()) + "T" + sdf_time.format(c.getTime()); 
	}

You can call it from within tJavaRow component like:

output_row.dt_datetime_string = myfuncs.DateTime(input_row.dt_value);

But first test it. Adding years is a bit tricky. As you can see I had to change the starting date to be 1899-12-30 instead of a 1900-01-01, because I was getting an offset of 2 days always. Maybe the Excel and Java are not calculating the leap years in the same way or it might be the problem with time of the day at interval start (like is it 1900-01-01 day-end or day-start?) or something else... Anyway I think this should work.