topic convert time in Talend Studio

convert time

Anonymous — Sat, 16 Nov 2024 12:53:17 GMT

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

Re: convert time

Anonymous — Mon, 06 Jun 2011 14:09:22 GMT

I think you can use Java routine. Make your own function to convert your format.
Benoît

Re: convert time

Anonymous — Mon, 06 Jun 2011 14:14:01 GMT

As I am not really Java developer could you please give me some sugestion...
Thanks

Re: convert time

Anonymous — Mon, 06 Jun 2011 14:32:24 GMT

I can try to help you but, can you explain me first how to convert the format.
I didn't understand how you pass from 0.39886574074074 to 9:34:22.
Benoit.

Re: convert time

Anonymous — Mon, 06 Jun 2011 16:09:00 GMT

I am having this format in csv file which data I am mapping.
The format of time is what I stated 0.398865741 as cell was formated as general .
However, I know is easy if I just reformat cell to be Time but I am not aloud to change any date and I need to upload date in format as it is and just after in Talend can do any changed...
I have look how to do this using some talend components but wasnt' much luck that is reason why I asked for help...
thanks

Re: convert time

Anonymous — Mon, 06 Jun 2011 16:48:26 GMT

I still didn't understand what means your number 0.398865741 ?
My question was how you can conclude the number 0.39886574074074 is the folowing time 9:34:22 ?
?
0.39886574074074 -> 9:34:22
Thanks,
Benoît.

Re: convert time

Anonymous — Mon, 06 Jun 2011 17:09:59 GMT

This number was in my csv document which I have opend using excel.
When you check format cell for that number is showing you General option but when I chose Time option giving me time which I actually need but can not change in excel have to be changed in Talend when I created file delemeted and do mapping.
Please see images

Re: convert time

Anonymous — Mon, 06 Jun 2011 19:16:52 GMT

Sounds like your not sure what excel is doing to your data. If you can avoid sourcing data from excel, its ALWAYS a good idea to do so.
That said, if you're stuck reading excel files (I am so so sorry) -- here's the skinny

Excel stores dates as sequential numbers known as serial values. Excel stores times as decimal fractions because time is considered a portion of a day. Dates and times are values and so you can add, subtract, and include dates and times in other calculations.
For example, to determine the difference between two dates, you can subtract one date from the other. You can view a date as a serial value and a time as a decimal fraction by changing the format of the cell that contains the date or time to General format.

so its just fractional days... I can write a converter for that
This is in Perl cause I had it handy... if you're still stuck, I can convert to java for you

#!/usr/bin/perl -w
use strict;

my $excel_time = 0.398865741; #9:34:22
my $sec_in_day = 86400;
my $min_in_day = 1440;
my $hour_in_day = 24;
#int function just truncates decimals
my $h = int($excel_time * $hour_in_day); #hours are easy
my $m = int(($excel_time * $min_in_day) - $h * 60); #minuets, minus the hour portion
my $s = int(($excel_time * $sec_in_day) - (($h * 60 * 60) + $m * 60)); #seconds minus the hours and min portion

print "$excel_time --> $h:$m:$s \n";

Re: convert time

Anonymous — Tue, 07 Jun 2011 08:52:02 GMT

thanks for this perl but I am using java ..
do you mind to give me some java exampe. ..

Re: convert time

Anonymous — Tue, 07 Jun 2011 09:11:04 GMT

I do know what excel doing to my date but the problme as our client sending us files in that fomat and we are not aloud to change any orginal document just to use and do ETL data for our use and convert by talend but not touching any orginal document..
I am not java developer I am more Database person and now I am stuck with this java code, had tried to find some solution but did not have much luck..
will be great if you can help ..
Thanks

Re: convert time

Anonymous — Tue, 07 Jun 2011 09:11:33 GMT

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

Re: convert time

Anonymous — Tue, 07 Jun 2011 09:14:43 GMT

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

Re: convert time

Anonymous — Tue, 07 Jun 2011 09:43:24 GMT

Thanks

Re: convert time

Anonymous — Tue, 07 Jun 2011 12:14:58 GMT

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

Re: convert time

Anonymous — Tue, 07 Jun 2011 13:10:43 GMT

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.

Re: convert time

Anonymous — Tue, 07 Jun 2011 13:59:23 GMT

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

Re: convert time

Anonymous — Thu, 09 Jun 2011 09:45:23 GMT

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

Re: convert time

Anonymous — Thu, 09 Jun 2011 15:53:03 GMT

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!

Re: convert time

Anonymous — Thu, 09 Jun 2011 16:52:20 GMT

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

Re: convert time

vladika — Fri, 10 Jun 2011 13:22:11 GMT

?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.