Timestamp in the date field is not picking correct value

[#1]

Orders_t:
LOAD WorkOrder,
 Item,
 Type,
 Date,
 Time
FROM C:\Users\Book1.xls (biff, embedded labels, table is [Sheet1$]);

Orders:
LOAD WorkOrder,
min(timestamp(date#(Date,'MM/DD/YYYY') & ' ' & (Time),'MM/DD/YYYY h:mm:ss[.fff]')) as MinDate,
max(timestamp(date#(Date,'MM/DD/YYYY') & ' ' & (Time),'MM/DD/YYYY h:mm:ss[.fff]')) as MaxDate
resident Orders_t
group by WorkOrder;

Load*,
interval((t1-t2),'D')*24 as diff;
LOAD WorkOrder,
date(MaxDate) as t1,
date(MinDate) as t2,
time(MaxDate) as t3,
time(MinDate) as t4
resident Orders

I need to subtract t1-t2 and find no of hours .

I am getting the values from diff field

3.4911111111869
22.999999999884

I am loading the sample data

WorkOrder	Item	Type	Date	Time
100961	10	P01	5/4/2009	14:00:00
100961	10	P01	5/5/2009	8:00:00
100961	10	P01	5/5/2009	11:45:00
100961	10	P01	5/5/2009	13:00:00
200863	10	P01	4/4/2009	12:00:00
200863	10	P01	4/4/2009	8:30:32
200863	10	P01	4/4/2009	9:15:00

timestamp is picking fraction of values. 4/4/2009 - 4/4/2009 needs to be 0 .I am getting value 0.14546296296612

5/5/2009-5/4/2009 needs to be 1 day - it is picking 0.95833333332848

I have to find first the minimum date and time and max date and time and subract Maxdatetime-Mindatetime and find out how many hours took for that order.

Advance thanks for your help.

[johnw]

So you just want to ignore the time of day?

So April 5, 2009 00:01:00 - April 4, 2009 23:59:00 = 1 day = 24 hours (even though it was actually 2 minutes)?

And April 5, 2009 23:59:00 - April 4, 2009 00:01:00 = 1 day = 24 hours (even though it was actually 47 hours and 58 minutes)?

Flooring a timestamp will give you just the date. So the number of days between the two would be this:

floor(Timestamp2)-floor(Timestamp1)

And for hours, just multiply by 24:

(floor(Timestamp2)-floor(Timestamp1))*24

[stevedark]

Hi There,

The code that John has posted will, I believe, resolve the issue fine. It may make the code more readable however to use the "daystart" function in the place of floor. This does exactly as the same suggests and returns the date part of the field, with the time stripped off. In practical terms floor does exactly the same, as all dates are stored as decimal numbers anyway, with whole numbers relating to days and fractions relating to time.

There are absolutely loads of date functions in the scripting language, and a browse through that section of the manual would no doubt be of benefit to anyone requiring this sort of functionality.

Cheers,
Steve

[#4]

Hi

Rather than explain I have knocked up a quick example which does what I think you need.

Please see attached.

Basically use Maketime function to build time then add this to a true date.

Hope this helps

regards

Derek

[#5 · Accepted solution]

Thanks for all your help.It is working fine .

[johnw]

Steve Dark wrote:It may make the code more readable however to use the "daystart" function in the place of floor.

Heh. I'm so used to thinking of dates and times as numbers that I never even noticed the existence of the daystart() function. You're right that that would be more readable. I'll start using that instead. Thanks.