MySQL在特定时间间隔内创建的日期时间之间的平均小时数 [英] MySQL average number of hours between created datetimes for a specific time interval

问题描述

我有一个表，其中包含一个名为"created"的字段，这是一个日期时间字段.

I have a table with a field called "created" which is a datetime field.

假设NOW()在2013-07-21的午夜，所以NOW()= 2013-07-21 23:59:59

Assume NOW() is midnight on 2013-07-21, so NOW() = 2013-07-21 23:59:59

现在假设我查询所有记录 在DATE_SUB(NOW()，间隔4天)和NOW()之间创建的位置

Now let's say I query for all records WHERE created BETWEEN DATE_SUB(NOW(), INTERVAL 4 DAYS) AND NOW()

比方说，返回的结果如下:

Let's say that returns results like this:

1. 2013-07-18 08:00:00
2. 2013-07-19 08:00:00
3. 2013-07-20 08:00:00
4. 2013-07-21 08:00:00

我想将我使用的间隔(4天)的开始和结束日期时间添加到该结果集中，所以现在我有了:

I want to add the start and end datetime for the interval I used (4 days) to that result set, so now I have:

1. 2013-07-18 00:00:00(4天前来自NOW())
2. 2013-07-18 08:00:00
3. 2013-07-19 08:00:00
4. 2013-07-20 08:00:00
5. 2013-07-21 08:00:00
6. 2013-07-21 23:59:59(NOW())

现在我想要一个查询，该查询将为我提供这6个日期时间结果之间的平均小时数.

And now I want a query that will give me the average amount of hours between those 6 datetime results.

即(8 + 24 + 24 + 24 + 24 + 16)/6，平均20小时.

That is (8 + 24 + 24 + 24 + 24 + 16) / 6 which is an average of 20 hours.

我在堆栈上发现了这个问题

I found this on stack over flow

SELECT
TIME_TO_SEC(TIMEDIFF(end,start)) AS timediff
FROM
Sessions
GROUP BY
DATE(start)

此查询的问题是，我必须每次在日期中运行6次(在PHP循环中)，然后将所有结果相加和/6，然后/乘以3600.

The problem with this query is that I'd have to run it 6 times passing in the dates each time (in a PHP loop) and then add all the results and / 6 and then / by 3600.

如何仅使用mySQL就能得到我想要的结果?

How can get the result I want just using mySQL?

谢谢！

请稍作澄清:

假设两件事.

1. 用户将选择一个日期范围(在这种情况下为7月21日至7月18日)

1. A user is going to select a date range (in this case July 21st to July 18th)

其他用户正在使用其他服务，该服务在每次使用该服务时都会生成创建的记录.

Other users are using a different service that generates that created record each time they use the service.

第一个用户想知道第二个用户在所选日期范围内平均使用该第二项服务的频率(以小时为单位).

The first user wants to know how often on average (in hours) that the second used that second service between the selected date range.

因此，它需要考虑2013年7月18日00:00:00到2013年7月18日08:00:00之间的时间(这8小时很重要)，最后的16小时也是如此，因为用户在这些时间段内未使用该服务.

So, it needs to account for the time between 2013-07-18 00:00:00 and 2013-07-18 08:00:00 (those 8 hours matter) and so do the 16 hours at the end, because the user did not use the service during those time periods.

基本上，我不仅想要4个初始创建的记录之间的平均小时数，我认为(如果我错了，请更正)这正是Gordon建议的.

Basically I don't only want the average amount of hours between the 4 initial created records, which I think (correct me if i'm wrong) that is what Gordon suggested.

推荐答案

平均值是第一条记录与最后一条记录之间的差除以计数和一些数字.

The average is the difference between the first and last records divided by the count plus some number.

SELECT (UNIX_TIMESTAMP(max(date(created)+1), min(date(created)))/1000)/(count(*)+2) AS timediffsecs
FROM Sessions
WHERE created BETWEEN DATE_SUB(NOW(), INTERVAL 4 DAYS) AND NOW()

这是做什么的?首先，它没有在数据中添加其他记录.相反，它只是舍入较早的日期并舍入较晚的日期.从某个时间点开始，UNIX_TIMETAMP会产生以毫秒为单位的值.取最大和最小之间的差异.最后，除以遇到的行数加2(我认为应该是计数加1，但您的问题是计数加2).

What is this doing? First, it is not adding additional records to the data. Instead, it is just rounding down the earlier date and rounding up the later date. UNIX_TIMETAMP produces values in milliseconds since some point in time. Take the difference between the biggest and smallest. Finally, divide by the number of rows encountered plus 2 (I think it should be the count plus 1, but your question says count plus 2).

这篇关于MySQL在特定时间间隔内创建的日期时间之间的平均小时数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！