SXI Forum

SeanR

Administrator

Registered: 20-11-2018

Posts: 148

Subtracting Dates

Date Subtraction

<sxi:DataDefinition name="CalculateTotalTimeOnsite">
  <sxi:Fields>
	<!-- Convert the Date to Epoch -->
	<sxi:Field name="ActualWorkStartDate">
	  <sxi:Rules>
		<sxi:DateToEpoch InputDateFormat="yyyy-MM-dd HH:mm"/>
	  </sxi:Rules>
	  <sxi:OutputField>EpochActualWorkStartDate</sxi:OutputField>
	</sxi:Field>
	<!-- Convert the Date to Epoch -->
	<sxi:Field name="FinalDateTime">
	  <sxi:Rules>
		<sxi:DateToEpoch InputDateFormat="yyyy-MM-dd HH:mm"/>
	  </sxi:Rules>
	  <sxi:OutputField>EpochFinalDateTime</sxi:OutputField>
	</sxi:Field>
	<!-- Subtract the Epoch Final Date From Epoch Start Date to get the difference in seconds -->
	<sxi:Field name="EpochFinalDateTime">
	  <sxi:Rules>
		<sxi:Calculate>
		  <sxi:Expression operator="-">
			<sxi:Operand inputField="yes">EpochActualWorkStartDate</sxi:Operand>
		  </sxi:Expression>
		</sxi:Calculate>
	  </sxi:Rules>
	  <sxi:OutputField>EpochTotalTime</sxi:OutputField>
	</sxi:Field>
	<!-- Convert the seconds difference to minutes -->
	<sxi:Field name="EpochTotalTime">
	  <sxi:Rules>
		<sxi:Calculate>
		  <sxi:Expression operator="/">
			<sxi:Operand>60</sxi:Operand>
		  </sxi:Expression>
		</sxi:Calculate>
	  </sxi:Rules>
	  <sxi:OutputField>TimeOnsite</sxi:OutputField>
	</sxi:Field>
  </sxi:Fields>
</sxi:DataDefinition>

Credit goes to Lauren.

KevinM

Moderator

Registered: 21-11-2018

Posts: 13

Re: Subtracting Dates

Nope does not work on early dates.
Not sure from which date this will work but check start date 1989-05-01 01:07:54 and end date 1989-05-02 01:07:54 for example

KevinM

Moderator

Registered: 21-11-2018

Posts: 13

Re: Subtracting Dates

This is happening due to a bug in the code. The developer thought a good way to convert the Epoch milliseconds to Epoch seconds would be to truncate the field to a length of 10. Obviously this would not work on earlier dates because the truncation removes significant digits resulting in incorrect times.

Can someone think of a workaround for this while we wait for the code to be fixed??

SeanR

Administrator

Registered: 20-11-2018

Posts: 148

Re: Subtracting Dates

Nope does not work on early dates.
Not sure from which date this will work but check start date 1989-05-01 01:07:54 and end date 1989-05-02 01:07:54 for example

This has been fixed in v2.1P9

There are now 2 rules:

   - DateToEpoch:          This will return the number of SECONDS since 1970-01-01 00:00:00
   - DateToMilliseconds: This will return the number of MILLISECONDS since 1970-01-01 00:00:00