在日期等于或大于90天之前获取数据 [英] Get data when date is equal to or greater than 90 days ago

问题描述

我想知道这里是否有人可以帮助我处理BigQuery.当最后一次互动时间等于或大于90天之前，我试图从数据集中提取日期，电子邮件和最后一次互动时间.

I wonder if anyone here can help with a BigQuery piece I am working on. I'm trying to pull the date, email and last interaction time from a dataset when the last interaction time is equal to or greater than 90 days ago.

我有以下查询:

SELECT
  date,
  user_email,
  DATE_FROM_UNIX_DATE(gmail.last_interaction_time) AS Last_Interaction_Date,
  DATE_ADD(CURRENT_DATE(), INTERVAL -90 DAY) AS Days_ago
FROM
  `bqadminreporting.adminlogtracking.usage`
WHERE
  'Last_Interaction_Date' >= 'Days_ago'

但是，我遇到以下错误:

However, I run into the following error:

DATE值超出允许范围:从0001-01-01到9999-12-31

DATE value is out of allowed range: from 0001-01-01 to 9999-12-31

据我所知，这是有道理的-不能完全确定为什么会抛出错误吗?

As far as I can see, it makes sense - so not entirely sure why its throwing out an error?

推荐答案

大概是您的问题是 DATE_FROM_UNIX_DATE().没有样本数据，实际上不可能确定问题所在.

Presumably, your problem is DATE_FROM_UNIX_DATE(). Without sample data, it is not really possible to determine what the issue is.

但是，您无需转换为日期即可执行此操作.您可以在Unix秒空间中完成所有工作:

However, you don't need to convert to a date to do this. You can do all the work in the Unix seconds space:

select u.*
from `bqadminreporting.adminlogtracking.usage` u
where gmail.last_interaction_time >= unix_seconds(timestamp(current_date)) - 90 * 60 * 60 * 24

请注意，我怀疑问题是 last_interaction_time 的确以毫秒或微秒或其他单位来度量.这样可以防止出现错误，但可能无法满足您的要求.

Note that I suspect that the issue is that last_interaction_time is really measured in milliseconds or microseconds or some other unit. This will prevent your error, but it might not do what you want.

这篇关于在日期等于或大于90天之前获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！