R中SQLDF中的日期之间的差异 [英] Difference between dates in SQLDF in R

问题描述

我正在使用R包SQLDF，并且无法找到两个日期时间变量之间的天数.变量 ledger_entry_created_at 和 created_at 是Unix时代，当我尝试将其转换为 julianday 后减去它们时，我返回了一个 NA的向量的.

我看过

  SELECT strftime('％Y-％m-％d％H:％M:％S'，l.created_at，'unixepoch')ledger_entry_created_at，l.ledger_entry_id，l.account_id，l.amount，a.user_id，u.created_at从分类帐lLEFT JOIN帐户a开启l.account_id = a.account_idLEFT JOIN用户u开启a.user_id = u.user_id 

解决方案

这个答案很简单，但是如果您已经有两个UNIX时间戳，并且想要找出它们之间已经间隔了多少天，则可以简单地使用单位为秒的差异(原始单位)，然后转换为天，例如

  SELECT(l.created_at-u.created_at)/(3600 * 24)AS diff-这里可能还有其他专栏从分类帐lLEFT JOIN帐户a开启l.account_id = a.account_idLEFT JOIN用户u开启a.user_id = u.user_id; 

我不知道您当前的方法为什么会失败，因为屏幕截图中的时间戳应该是SQLite的 julianday 函数的有效输入.但是，同样，您可能不需要这么复杂的路线即可获得想要的结果.

I am using the R package SQLDF and am having trouble finding the number of days between two date time variables. The variables ledger_entry_created_at and created_at are Unix Epochs and when I try to subtract them after casting to julianday, I return a vector of NA's.

I've taken a look at this previous question and didn't find it useful since my answer to be given in SQL for reasons that are outside the scope of this question.

If anyone could help me figure out a way to do this inside SQLDF I would be grateful.

EDIT:

    SELECT strftime('%Y-%m-%d %H:%M:%S', l.created_at, 'unixepoch') ledger_entry_created_at, 
      l.ledger_entry_id, l.account_id, l.amount, a.user_id, u.created_at
  FROM ledger l
  LEFT JOIN accounts a
  ON l.account_id = a.account_id
  LEFT JOIN users u
  ON a.user_id = u.user_id

解决方案

This answer is trivial, but if you already have two UNIX timestamps, and you want to find out how many days have elapsed between them, you can simply take the difference in seconds (their original unit), and convert to days, e.g.

SELECT
    (l.created_at - u.created_at) / (3600*24) AS diff
    -- any maybe other columns here
FROM ledger l
LEFT JOIN accounts a
    ON l.account_id = a.account_id
LEFT JOIN users u
    ON a.user_id = u.user_id;

I don't know why your current approach is failing, as the timestamps you have in the screen capture should be valid inputs to SQLite's julianday function. But, again, you may not need such a complicated route to get the result you want.

这篇关于R中SQLDF中的日期之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！