convert time

One Star

convert time

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
One Star

Re: convert time

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

Re: convert time

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

Re: convert time

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.
One Star

Re: convert time

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
One Star

Re: convert time

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.
One Star

Re: convert time

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

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 Smiley Wink
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";
One Star

Re: convert time

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

Re: convert time

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
One Star

Re: convert time

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

Re: convert time

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

Re: convert time

Thanks
One Star

Re: convert time

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
One Star

Re: convert time

Maybe if you try these code Smiley Wink :
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.
One Star

Re: convert time

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

Re: convert time

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

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!
One Star

Re: convert time

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
Four Stars

Re: convert time

?eljka? Each time you have a problem you wake up the whole cavalry Smiley Very Happy
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.
One Star

Re: convert time

Hi everyone,
Thank you guys for all your helps and you are just grate..
yes Vladika this routine working I just need it to add on this to my code and also on the top of the routine need it to add
import java.text.ParseException;
However, this was just as you said part of testing and all these is good lesson for me as I am not java developer , I am database (sql and t-sql) and VB one.
I am so glad as I am able to wake up all you cavalry and simple with my "problems" .. I just always put little bit 'hint' of my Serbian charm with all my question..
Thanks one more time...
?eljka