Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have date fields like start date and end date in my table
start date end date
2016-10-23 00:00:00:00 2016-10-24 23:58:59:00
2016-11-24 00:00:00:00 2017-01-10 23:59:59:00
2016-12-01 00:00:00;00 2016-12-31 23:58:59:00
2016-12-28 00:00:00:00 2017-05-1 00:00:00:00
2017-01-01 00:00:00:00 2017-08-08 23:58:58:00
i want the total hours between these date range and i want to this in the script level
for Example : start date:2016-10-23 00:00:00:00 and end date: 2016-10-24 23:58:59:00 i want as total hours spent was 48 hours
can any one help me on this
Thanks
Mahesh
Data:
Load *, DifferenceInTime * 24 as Difference;
Load
Interval(TimeStamp#([end date],'YYYY-MM-DD hh:mm:ss:ff') - TimeStamp#([start date],'YYYY-MM-DD hh:mm:ss:ff')) as DifferenceInTime,
TimeStamp#([start date],'YYYY-MM-DD hh:mm:ss:ff') as [start date],
TimeStamp#([end date],'YYYY-MM-DD hh:mm:ss:ff') as [end date]
Inline
[
start date, end date
2016-10-23 00:00:00:00, 2016-10-24 23:58:59:00
2016-11-24 00:00:00:00, 2017-01-10 23:59:59:00
2016-12-01 00:00:00;00, 2016-12-31 23:58:59:00
2016-12-28 00:00:00:00, 2017-05-1 00:00:00:00
2017-01-01 00:00:00:00, 2017-08-08 23:58:58:00
];
Hi manish
Thank you for fast response . can you please explain these syntax
1) I have converted start date and end date into timestamp using TimeStamp# function.
Read below blog post to understand difference between TimeStamp and TimeStamp#.
2) Now you can find the difference between them. In case if we need to show more than 24 hours, use Interval function.
This will give you difference in hh:mm:ss format.
3) Now multiply the result by 24 which will convert it to hours ...