Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Community,
I have field with hh:mm:ss, which I need to convert to give only minutes as a single output.
I have tried the following, but it doesnt work:
//----------------//
(Subfield(inc_open_date_timestamp, ':', 1)*60 +
Subfield(inc_open_date_timestamp, ':', 2) +
Subfield(inc_open_date_timestamp, ':', 3))/60 as inc_Open_Time_Minutes,
//----------------//
Interval(frac(timestamp#(SUM (inc_open_date_timestamp), 'YYYY/MM/DD hh:mm:ss')),'mm') as Opened_date_Mins
//---------------//
Can someone please guide.
Regards,
Akbar
Here's the solution:
TempTable:
NoConcatenate
LOAD *,
Timestamp(Time#(fieldname, 'hh:mm:ss'), 'hh:mm') as Temp_new_fieldname,
Evaluate (SubField (Timestamp(Time#(fieldname, 'hh:mm:ss'), 'hh:mm'), ':' , 1) * 60) +
(SubField (Timestamp(Time#(fieldname, 'hh:mm:ss'), 'hh:mm'), ':' , 2) ) as New_Field_in_MINS
Resident Table name;
You can try this in the main table as well if your timestamp field is in the right format (hh:mm:ss). I had a date attached to the field, so I had to change it in the main load and do a resident with that new timestamp field.
Regards,
a
An interval is really just a number (with the unit being days) formatted in a specific way, so math should work here:
Time*24*60 = Time in Minutes
Hi,
Thanks for the reply, however I am not getting the same output. Not sure what part I am missing. Have attached my output for reference.
The expression that I am using //inc_resolved_date_timestamp*24*60//
and the dimension field //inc_resolved_date_timestamp//
Look forward to your solution
Regards,
Akbar
If your field is a datestamp rather than an interval as you originally stated, this won't work because the date part of the field is also a number. You'll have to get rid of the date part, e.g. by using Frac() or some other method.
Here's the solution:
TempTable:
NoConcatenate
LOAD *,
Timestamp(Time#(fieldname, 'hh:mm:ss'), 'hh:mm') as Temp_new_fieldname,
Evaluate (SubField (Timestamp(Time#(fieldname, 'hh:mm:ss'), 'hh:mm'), ':' , 1) * 60) +
(SubField (Timestamp(Time#(fieldname, 'hh:mm:ss'), 'hh:mm'), ':' , 2) ) as New_Field_in_MINS
Resident Table name;
You can try this in the main table as well if your timestamp field is in the right format (hh:mm:ss). I had a date attached to the field, so I had to change it in the main load and do a resident with that new timestamp field.
Regards,
a