title: datatable not create correct value when using date to calculate
body: <p>I have a <code>data</code> and want to create some new columns based on original <code>data</code>.  </p>
  
  <pre><code>Time1            Time2         value    number
  2015-01-01       NA            1724     1
  2015-01-01       2015-01-03    2946     1
  2015-01-01       2015-01-03    -2946    -1
  2016-12-09       NA            -2182    -1
  </code></pre>
  
  <p>I explain what I want to do:<br>
  1. I want to create 4 new columns(<code>Begin</code>, <code>End</code>, <code>MonthEnd</code>, <code>value2</code>).<br>
  2. <code>Time1</code>, <code>Time2</code>, <code>Begin</code>, <code>End</code>, <code>MonthEnd</code> are date format(<code>POSIXct format</code>)<br>
  3. <code>value</code> and <code>value2</code> are numeric.<br>
  4. <code>number</code> is character.<br>
  5. IF <code>number</code>=1 THEN <code>Begin</code>=<code>Time1</code>; IF <code>number</code>=-1 and <code>Time2</code> not equal to NA THEN <code>Begin</code>=<code>Time2</code>; else <code>Begin</code>=<code>Time1</code>.<br>
  6. <code>End</code> is next year of <code>Time1</code>. (ex. <code>Time1</code>=2015-01-01, <code>End</code>=2016-01-01)<br>
  7. <code>MonthEnd</code> is the <em>last day of month</em> of <code>Time1</code>. (ex. <code>Time1</code>=2015-01-01, <code>MonthEnd</code>=2015-01-31. Since last day of Jan is 31)   </p>
  
  <p>First, I create these new columns:  </p>
  
  <pre><code>data[,Begin:= Time2][number==1, Begin:= Time1][is.na(Begin), Begin:= Time1][]
  data[,End:=Time1+months(12)]
  data[,MonthEnd:=Time1]
  day(data$MonthEnd) &lt;- days_in_month(data$Time1)
  </code></pre>
  
  <p>Therefore, the result based on above code is:  </p>
  
  <pre><code>Time1           Time2        Begin         End            MonthEnd          value    number
  2015-01-01      NA           2015-01-01    2016-01-01     2015-01-31        1724     1
  2015-01-01      2015-01-03   2015-01-01    2016-01-01     2015-01-31        2946     1
  2015-01-01      2015-01-03   2015-01-03    2016-01-01     2015-01-31        -2946    -1
  2016-12-09      NA           2016-12-09    2017-12-09     2016-12-31        -2182    -1
  </code></pre>
  
  <p>Second, I create <code>value2</code> based on above new <code>data</code>:  </p>
  
  <pre><code>data[, value2 := value*pmax(0,MonthEnd - Begin) / as.numeric(End - Time1)]
  </code></pre>
  
  <p>I get:  </p>
  
  <pre><code>value2
  12242762
  20920636
  -19525926.58  
  -11363138.6
  </code></pre>
  
  <p>However, this is false.<br>
  Take first obs. as an example. This is the true value.  </p>
  
  <pre><code>Time1 = as.Date("2015-01-01")
  Begin = as.Date("2015-01-01")
  End   = as.Date("2016-01-01")
  MonthEnd = as.Date("2015-01-31")
  value2 = value * max(0,MonthEnd - Begin) / as.numeric(End - Time1)
  value2
  [1] 141.6986
  </code></pre>
  
  <p>Here is the <code>str(data)</code>:  </p>
  
  <pre><code>Classes ‘data.table’ and 'data.frame':  105907 obs. of  7 variables:
  $ Time1       : POSIXct, format: "2015-01-01" "2015-01-01" "2015-01-01" "2015-01-01" ...
  $ Time2       : POSIXct, format: NA "2015-01-03" "2015-01-03" NA ...
  $ Begin       : POSIXct, format: "2015-01-01" "2015-01-01" "2015-01-01" "2015-01-01" ...
  $ End         : POSIXct, format: "2016-01-01" "2016-01-01" "2016-01-01" "2016-01-01" ...
  $ MonthEnd    : POSIXct, format: "2015-01-31" "2015-01-31" "2015-01-31" "2015-01-31" ...
  $ value       : num  1724 2946 -2946 -2182 ...
  $ number      : chr  "1" "1" "-1" "-1" ...
  - attr(*, ".internal.selfref")=&lt;externalptr&gt; 
  </code></pre>
  
  <p>Moreover, I test whether date format(<code>POSIXct format</code>) influences the result. So I try:  </p>
  
  <pre><code>Time1 &lt;- as.POSIXct("2015-01-01")
  Begin &lt;- as.POSIXct("2015-01-01")
  End &lt;- as.POSIXct("2016-01-01")
  MonthEnd &lt;- as.POSIXct("2015-01-31")
  value &lt;- 1724
  value2 &lt;- value * pmax(0, MonthEnd - Begin) / as.numeric(End - Time1)
  value2
  [1] 141.6986
  </code></pre>
  
  <p>Thus, I think this problem is due to <code>data.table</code>.<br>
  Is <code>datatable</code> cannot create correct value with calculating date? Bug? Or my code is wrong?<br>
  Any suggestion? Appreciate.</p>
  
  <p><strong>UPDATE</strong>  </p>
  
  <pre><code>sessionInfo()
  R version 3.3.3 (2017-03-06)
  Platform: i386-w64-mingw32/i386 (32-bit)
  Running under: Windows 7 (build 7601) Service Pack 1
  
  locale:
  [1] LC_COLLATE=Chinese (Traditional)_Taiwan.950  LC_CTYPE=Chinese (Traditional)_Taiwan.950   
  [3] LC_MONETARY=Chinese (Traditional)_Taiwan.950 LC_NUMERIC=C                                
  [5] LC_TIME=Chinese (Traditional)_Taiwan.950   
  
  attached base packages:
  [1] stats     graphics  grDevices utils     datasets  methods   base     
  
  other attached packages:
  [1] dplyr_0.5.0       lubridate_1.6.0   readxl_1.0.0      data.table_1.10.4
  
  loaded via a namespace (and not attached):
   [1] lazyeval_0.2.0   magrittr_1.5     R6_2.2.0         assertthat_0.1   DBI_0.5-1        tools_3.3.3      tibble_1.2      
   [8] Rcpp_0.12.9      cellranger_1.1.0 stringi_1.1.2    stringr_1.1.0    
  </code></pre>
  
  <p><strong>Problem Solved</strong><br>
  The problem is <code>POSIXct, format</code>.<br>
  R considers these date columns to calculate with <code>seconds units</code>.<br>
  Thus, using <code>difftime()</code> and set <code>units = 'day'</code> will work.<br>
  Or, <code>as.Date()</code> will work as well.</p>