
Anonymous
Not applicable
2011-06-06
08:16 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
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
672 Views
20 Replies

Anonymous
Not applicable
2011-06-07
05:11 AM
Author
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
}
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;
}
227 Views

Anonymous
Not applicable
2011-06-07
05:14 AM
Author
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You can put these function in a routine class, and next call it from your Talend job.
227 Views

Anonymous
Not applicable
2011-06-07
05:43 AM
Author
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks
227 Views

Anonymous
Not applicable
2011-06-07
08:14 AM
Author
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
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
227 Views

Anonymous
Not applicable
2011-06-07
09:10 AM
Author
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
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.
227 Views

Anonymous
Not applicable
2011-06-07
09:59 AM
Author
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
}
}
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;
}
}
227 Views

Anonymous
Not applicable
2011-06-09
05:45 AM
Author
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
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
227 Views

Anonymous
Not applicable
2011-06-09
11:53 AM
Author
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
edit: syntax error!
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!
227 Views

Anonymous
Not applicable
2011-06-09
12:52 PM
Author
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
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
227 Views

Contributor
2011-06-10
09:22 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
?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:
Now add new function:
You can call it from within tJavaRow component like:
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.
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.
227 Views
