Filtering Databases with dplyr and Unix Timestamps
<p>I'm working with a large database (via <code>dplyrimpaladb</code>) and <code>dplyr</code>. Due to this I need to filter by date all of which are given in Unix timestamps. While I can convert it locally as </p>

<pre><code>time_t = as.Date(as.POSIXct(time_t/1000, origin = '1970-01-01', tz = 'UTC')))` 
</code></pre>

<p>This does not work when communicating with DB; I need to translate the following into <code>dplyr</code>. </p>

<pre><code>dau &lt;- bb %&gt;%
  tbl(sql("SELECT
             device_token_s,
             to_date(from_unixtime(cast(collector_date_t/1000 as bigint))) AS dte
           FROM bb.sys_app_open
           WHERE 
             build_type_n = 1
             AND to_date(from_unixtime(cast(collector_date_t/1000 as bigint))) &gt;=  '2016-02-26'
           GROUP BY 
             device_token_s,
             to_date(from_unixtime(cast(collector_date_t/1000 as bigint)))")) %&gt;%
  collect()
</code></pre>

<p>The closest I could get was,</p>

<pre><code>dau.df &lt;- bb %&gt;% 
  tbl('sys_app_open') %&gt;%
  select(device_token_s, 
         sql('to_date(from_unixtime(cast(collector_date_t/1000 as bigint))) AS dte')) %&gt;%
  filter(build_type_n == 1, 
         sql("to_date(from_unixtime(cast(collector_date_t/1000 as bigint))) &gt;=  '2016-02-26' ")) %&gt;%
  #mutate(collector_date_t = sql('to_date(from_unixtime(cast(collector_date_t/1000 as bigint)))')) %&gt;%
  group_by(device_token_s, sql('to_date(from_unixtime(cast(collector_date_t/1000 as bigint)))')) %&gt;%
  collect()
</code></pre>

<p>But I receive a  </p>

<blockquote>
<pre><code>Error: All select() inputs must resolve to integer column positions.
The following do not:
*  sql("to_date(from_unixtime(cast(collector_date_t/1000 as bigint))) as dte")
</code></pre>
</blockquote>