mysql - 获取某个时间范围内 2 个日期的特定数据

Question

数据以 UTC 存储在数据库中，我必须将本地转换为 UTC 获取它，然后以本地时间显示结果。现在我来自印度，所以如果我想搜索今天的数据，我必须查询 UTC 时间 3 月 22 日下午 6:30 到 3 月 23 日下午 6:30 。现在假设我想检查一周中每一天的数据，截至目前，即本地时间下午 4:30。现在我写了这个查询[简单来说，我的目标是获取每天中午 12 点到下午 4:30 的访客数量]

SELECT
            FLOOR(TIMESTAMPDIFF(HOUR, "2017-03-16 18:29:59",
            visit.date_created)/24) as dayofweek,
            DAYOFWEEK(visit.date_created) day_num,
   @rownum := @rownum + 1 as date_created_set_av,
            count(distinct(visit.pkey)) AS Visits,
            sum(revenue) AS Revenue,
            sum(revenue) / count(distinct(visit.pkey)) as EPC
            FROM la_20.visit, la_20.action 
            cross join (select @rownum := 0) r   
            WHERE visit.pkey=action.pkey  and (visit.is_bot = 0)
            AND visit.date_created >="2017-03-16 18:29:59" 
            AND visit.date_created <="2017-03-23 18:29:59"  
            AND TIME(visit.date_created)<="16:30:00"
   GROUP BY dayofweek order by day_num

但它的作用是获取 2017-03-23 00:00:00 到 2017-03-23 16:30:00 之间的值。我需要的是显示 16 日到 23 日每天 18:29:59 到 16:30:00 的数据结果。请记住，我需要每天的结果，而不是一天的结果。谁能帮忙

Answer 1

您应该将 WHERE 子句更改为

WHERE visit.pkey=action.pkey  and (visit.is_bot = 0)
AND visit.date_created >="2017-03-22 18:29:59" and visit.date_created <="2017-03-23 14:29:59"  

已更新

• 今天午夜:DATEADD(d,0,DATEDIFF(d,0,GETDATE()))

• 今天下午 4 点:DATEADD(HOUR, 16, DATEADD(d,0,DATEDIFF(d,0,GETDATE())))

  WHERE visit.pkey=action.pkey  and (visit.is_bot = 0)
  AND visit.date_created >= DATEADD(d,0,DATEDIFF(d,0,GETDATE())) and visit.date_created <= DATEADD(HOUR, 16, DATEADD(d,0,DATEDIFF(d,0,GETDATE())))
  

更新 2:然后我猜你必须像这样扩展你的 where 子句。

WHERE visit.pkey=action.pkey  and (visit.is_bot = 0)
AND (
    (visit.date_created >= DATEADD(d, 0,DATEDIFF(d,0,GETDATE())) and visit.date_created <= DATEADD(HOUR, 16, DATEADD(d, 0,DATEDIFF(d,0,GETDATE()))))
 OR (visit.date_created >= DATEADD(d,-1,DATEDIFF(d,0,GETDATE())) and visit.date_created <= DATEADD(HOUR, 16, DATEADD(d,-1,DATEDIFF(d,0,GETDATE()))))
 OR (visit.date_created >= DATEADD(d,-2,DATEDIFF(d,0,GETDATE())) and visit.date_created <= DATEADD(HOUR, 16, DATEADD(d,-2,DATEDIFF(d,0,GETDATE()))))
 OR (visit.date_created >= DATEADD(d,-3,DATEDIFF(d,0,GETDATE())) and visit.date_created <= DATEADD(HOUR, 16, DATEADD(d,-3,DATEDIFF(d,0,GETDATE()))))
 OR (visit.date_created >= DATEADD(d,-4,DATEDIFF(d,0,GETDATE())) and visit.date_created <= DATEADD(HOUR, 16, DATEADD(d,-4,DATEDIFF(d,0,GETDATE()))))
 OR (visit.date_created >= DATEADD(d,-5,DATEDIFF(d,0,GETDATE())) and visit.date_created <= DATEADD(HOUR, 16, DATEADD(d,-5,DATEDIFF(d,0,GETDATE()))))
 OR (visit.date_created >= DATEADD(d,-6,DATEDIFF(d,0,GETDATE())) and visit.date_created <= DATEADD(HOUR, 16, DATEADD(d,-6,DATEDIFF(d,0,GETDATE()))))
 )