Conver hh:mm:ss into Minutes

[Akbar]

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

[Or]

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

[Akbar]

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

[Or]

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.

[Akbar · Accepted solution]

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