Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am trying to calculate the number of minutes between an incoming timestamp e.g. '
31-dec-14 08:05:00 |
and the fixed hour '16:15'
Defined some calculations in the load script:
TIME("Sessie - OK zaal in tijdstip",'hh:mm:ss') as Time_in,
MakeTime(16,15) as Cutoff_time,
INTERVAL(MakeTime(16,15) - Maketime(10,15),'mm') as Interval_static,
INTERVAL(MakeTime(16,15) - TIME("Sessie - OK zaal in tijdstip",'hh:mm:ss'),'mm') as Interval_dynamic,
TIME(INTERVAL(MakeTime(16,15) - TIME("Sessie - OK zaal in tijdstip",'hh:mm'),'mm')) as Interval_dynamic_format
Testresult "interval static" returns what I expected (240 min), but when trying to apply the same logic to my real time in, i am unable to get the correct answer.
Any ideas on how to do this? Thanks for your help and time.
May be this:
Interval((Time#('16:15', 'hh:mm') - Frac(TimeIn)), 'hh:mm:ss') as Interval
if your field is like 31-dec-14 08:05:00
interval(maketime(16,15) - frac(date#('31-dec-14 08:05:00', 'DD-MMM-YY hh:mm:ss')))
interval(maketime(16,15) - frac(date#(field, 'DD-MMM-YY hh:mm:ss')))
Hi,
Try like this
LOAD
*,
(MakeTime(16,15) - Frac(Date#(TimeFieldName, 'DD-MMM-YY hh:mm:ss'))) * 24 * 60 AS DurationInMinutes
FROM DataSource;
Note: Replace TimeFieldName with actual field name.
Regards,
Jagan.
Thanks for your suggestion, have modified load script as:
"Sessie - OK zaal in tijdstip" as Inputfield,
TIME("Sessie - OK zaal in tijdstip",'hh:mm:ss') as Time_in,
MakeTime(16,15) as Cutoff_time,
INTERVAL(MakeTime(16,15) - Maketime(10,15),'mm') as Interval_static,
INTERVAL(MakeTime(16,15) - TIME("Sessie - OK zaal in tijdstip",'hh:mm:ss'),'mm') as Interval_dynamic,
TIME(INTERVAL(MakeTime(16,15) - TIME("Sessie - OK zaal in tijdstip",'hh:mm'),'mm')) as Interval_dynamic_format,
interval(maketime(16,15) - frac(date#( "Sessie - OK zaal in tijdstip", 'DD/MM/YYYY hh:mm:ss'))) AS DurationInMinutes
However, the DurationInMinutes column does not show any values.
Thanks for your suggestion, have modified load script as:
"Sessie - OK zaal in tijdstip" as Inputfield,
TIME("Sessie - OK zaal in tijdstip",'hh:mm:ss') as Time_in,
MakeTime(16,15) as Cutoff_time,
INTERVAL(MakeTime(16,15) - Maketime(10,15),'mm') as Interval_static,
INTERVAL(MakeTime(16,15) - TIME("Sessie - OK zaal in tijdstip",'hh:mm:ss'),'mm') as Interval_dynamic,
TIME(INTERVAL(MakeTime(16,15) - TIME("Sessie - OK zaal in tijdstip",'hh:mm'),'mm')) as Interval_dynamic_format,
interval(maketime(16,15) - frac(date#( "Sessie - OK zaal in tijdstip", 'DD/MM/YYYY hh:mm:ss'))) AS DurationInMinutes
However, the DurationInMinutes column does not show any values.
Thanks for your suggestion but the calculated field does not display any values... i modified the load script:
"Sessie - OK zaal in tijdstip" as Inputfield,
TIME("Sessie - OK zaal in tijdstip",'hh:mm:ss') as Time_in,
MakeTime(16,15) as Cutoff_time,
INTERVAL(MakeTime(16,15) - Maketime(10,15),'mm') as Interval_static,
INTERVAL(MakeTime(16,15) - TIME("Sessie - OK zaal in tijdstip",'hh:mm:ss'),'mm') as Interval_dynamic,
TIME(INTERVAL(MakeTime(16,15) - TIME("Sessie - OK zaal in tijdstip",'hh:mm'),'mm')) as Interval_dynamic_format,
(MakeTime(16,15) - Frac(Date#( "Sessie - OK zaal in tijdstip" , 'DD-MM-YYYY hh:mm:ss'))) * 24 * 60 AS DurationInMinutes
Not sure you need the DATE#
The field seems to be recognised as date time already.
Thanks for your response. Blanks are gone but i get a negative and even the absolute value is larger then expected.
does the FRAC function around maketime(16,15) help?