MySql 查询 - 按日期范围分组？

发布于 2024-12-13 21:44:31 字数 1327 浏览 4 评论 0原文

我正在尝试编写一个查询，该查询将返回按日期范围分组的数据，并且想知道是否有一种方法可以在一个查询中完成此操作（包括计算），或者我是否需要编写三个单独的查询？我的表中的日期存储为 unix 时间戳。

例如，我的记录如下所示：

id  type    timestamp
312 87  1299218991
313 87  1299299232
314 90  1299337639
315 87  1299344130
316 87  1299348977
497 343 1304280210
498 343 1304280392
499 343 1304280725
500 343 1304280856
501 343 1304281015
502 343 1304281200
503 343 1304281287
504 343 1304281447
505 343 1304281874
566 90  1305222137
567 343 1305250276
568 343 1305387869
569 343 1305401114
570 343 1305405062
571 343 1305415659
573 343 1305421418
574 343 1305431457
575 90  1305431756
576 343 1305432456
577 259 1305441833
578 259 1305442234
580 343 1305456152
581 343 1305467261
582 343 1305483902

我正在尝试编写一个查询，该查询将查找“创建”日期在以下范围内的所有记录：

2011-05-01 和 2011-06-01（月）
2011-03-01 和2011-06-01（季度）
2010-05-01 和 2011-06-01（年）

我尝试了以下操作（在本例中，我硬编码了该月的 unix 值，看看是否可以让它工作...）：

SELECT COUNT(id) AS idCount,
MIN(FROM_UNIXTIME(timestamp)) AS fromValue, 
MAX(FROM_UNIXTIME(timestamp)) AS toValue
FROM uc_items 
WHERE ADDDATE(FROM_UNIXTIME(timestamp), INTERVAL 1 MONTH) 
      >= FROM_UNIXTIME(1304233200)

但是，它似乎不起作用，因为 fromValue 是： 2011-04-02 21:12 :56 并且 toValue 是 2011-10-25 06:20:14，显然，这不是 2011 年 5 月 1 日到2011 年 6 月 1 日。

原文

I'm trying to write a query that will return data grouped by date ranges and am wondering if there's a way to do it in one query (including the calculation), or if I need to write three separate ones? The dates in my table are stored as unix timestamps.

For example, my records look like this:

id  type    timestamp
312 87  1299218991
313 87  1299299232
314 90  1299337639
315 87  1299344130
316 87  1299348977
497 343 1304280210
498 343 1304280392
499 343 1304280725
500 343 1304280856
501 343 1304281015
502 343 1304281200
503 343 1304281287
504 343 1304281447
505 343 1304281874
566 90  1305222137
567 343 1305250276
568 343 1305387869
569 343 1305401114
570 343 1305405062
571 343 1305415659
573 343 1305421418
574 343 1305431457
575 90  1305431756
576 343 1305432456
577 259 1305441833
578 259 1305442234
580 343 1305456152
581 343 1305467261
582 343 1305483902

I'm trying to write a query that will find all records with a "created" date between:

2011-05-01 and 2011-06-01 (Month)
2011-03-01 and 2011-06-01 (Quarter)
2010-05-01 AND 2011-06-01 (Year)

I tried the following (in this case, I hardcoded the unix value for just the month to see if I could get it to work... ):

SELECT COUNT(id) AS idCount,
MIN(FROM_UNIXTIME(timestamp)) AS fromValue, 
MAX(FROM_UNIXTIME(timestamp)) AS toValue
FROM uc_items 
WHERE ADDDATE(FROM_UNIXTIME(timestamp), INTERVAL 1 MONTH) 
      >= FROM_UNIXTIME(1304233200)

But, it doesn't seem to work because the fromValue is: 2011-04-02 21:12:56 and the toValue is 2011-10-25 06:20:14, which, obviously, isn't a date between 5/1/2011 and 6/1/2011.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

半世晨晓 2024-12-20 21:44:31

这很有效：

SELECT COUNT(id) AS idCount,
   FROM_UNIXTIME(MIN(timestamp)) AS fromValue, 
   FROM_UNIXTIME(MAX(timestamp)) AS toValue
FROM uc_items
WHERE timestamp BETWEEN UNIX_TIMESTAMP('2011-05-01') AND UNIX_TIMESTAMP('2011-06-01 23:59:59')

此外，作为性能提示 - 避免在 WHERE 子句中将函数应用于表中的列（例如，您有 WHERE ADDDATE(FROM_UNIXTIME(timestamp))）。这样做将阻止 MySQL 使用 timestamp 列上的任何索引。

This aught to work:

SELECT COUNT(id) AS idCount,
   FROM_UNIXTIME(MIN(timestamp)) AS fromValue, 
   FROM_UNIXTIME(MAX(timestamp)) AS toValue
FROM uc_items
WHERE timestamp BETWEEN UNIX_TIMESTAMP('2011-05-01') AND UNIX_TIMESTAMP('2011-06-01 23:59:59')

Also, as a performance tip - avoid applying functions to columns in your tables in a WHERE clause (e.g you have WHERE ADDDATE(FROM_UNIXTIME(timestamp))). Doing that will prevent MySQL from using any indexes on the timestamp column.

回复收藏 0 原文

~没有更多了~