按天对 Unix 时间戳进行分组,产生不均匀间隔的组

发布于 2024-11-13 07:37:37 字数 1113 浏览 7 评论 0原文

我正在使用 MySQL 查询来提取一系列日期时间作为 Unix 时间戳(因为我将它们转换为 Javascript 时间)。我按“FROM_UNIXTIME”进行分组,如下所示:

SELECT 
    UNIX_TIMESTAMP(DateAndTime) as x, 
    Sum(If(Pass='Pass',1,0)) AS y, 
    Sum(If(Pass='Fail',1,0)) AS z, 
    Sum(If(Pass='Fail',1,0))/(Sum(If(Pass='Pass',1,0))+Sum(If(Pass='Fail',1,0))) AS a,
    cases.primaryApp 
FROM casehistory, cases
WHERE DATE_SUB(CURDATE(),INTERVAL 80 DAY) <= DateAndTime
AND cases.caseNumber = casehistory.caseNumber
AND cases.primaryApp = 'Promo'
GROUP BY FROM_UNIXTIME(x, '%Y-%m-%d')

虽然我希望时间戳均匀地返回(即每天/组之间的时间量相同),但我得到以下系列:

1300488140, 1300501520, 1300625099、1300699980

查询中的所有其他数据都是正确的,但由于时间戳的间距不规则,基于这些时间戳的条形图看起来非常糟糕。也许我在应用分组的方式上做错了什么?


谢谢您的回复。我的查询“有意义”,因为它产生了可以绘制的结果(分组是在日期和时间值的 x 别名上完成的),但问题是从数据库中提取 Unix 时间戳并按天分组返回了一系列时间戳他们之间的距离不相等。

我通过从日期时间 MySQL 字段中仅提取日期(没有时间)来解决这个问题,然后 - 在 PHP 中 - 将一个空时间连接到日期,将结果字符串转换为时间,然后将整个 shebang 乘以 1000 以返回我绘制图表所需的 JavaScript 时间如下:

x = x . ' 00:00:00';
x = strtotime(x) * 1000;

答案让我走上了正轨;我会接受的。我的图表现在看起来很完美。

I'm using a MySQL query to pull a range of datetimes as a Unix Timestamp (because I'll be converting them to Javascript time). I'm grouping by 'FROM_UNIXTIME' as below:

SELECT 
    UNIX_TIMESTAMP(DateAndTime) as x, 
    Sum(If(Pass='Pass',1,0)) AS y, 
    Sum(If(Pass='Fail',1,0)) AS z, 
    Sum(If(Pass='Fail',1,0))/(Sum(If(Pass='Pass',1,0))+Sum(If(Pass='Fail',1,0))) AS a,
    cases.primaryApp 
FROM casehistory, cases
WHERE DATE_SUB(CURDATE(),INTERVAL 80 DAY) <= DateAndTime
AND cases.caseNumber = casehistory.caseNumber
AND cases.primaryApp = 'Promo'
GROUP BY FROM_UNIXTIME(x, '%Y-%m-%d')

While I'd expected my timestamps to be returnd evenly spaced (that is, same amount of time between each day/group), I get the following series:

1300488140, 1300501520,
1300625099, 1300699980

All the other data from the query is correct, but because the spacing of the timestamps is irregular, a bar chart based on these stamps looks pretty awful. Perhaps I'm doing something wrong in the way I apply the grouping?


Thank you for the reply. My query 'made sense' in that it produced that could be plotted (the grouping was done on the x alias for the dateandtime value), but the problem was that pulling a Unix timestamp from the database and grouping by day returned a series of timestamps that did not have equal distance between them.

I solved this by pulling only the day (without the time) from the datetime MySQL field, then - in PHP - concatenating an empty time to the date, converting the resulting string to a time, then multiplying the whole shebang by 1000 to return the Javascript time I needed for the charting, like this:

x = x . ' 00:00:00';
x = strtotime(x) * 1000;

The answer put me on the right track; I'll accept it. My chart looks perfect now.

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

扫码二维码加入Web技术交流群

发布评论

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

评论(1

咿呀咿呀哟 2024-11-20 07:37:37

问题很困惑。

您的 SQL 语句没有任何意义 - 您正在按 select 语句中找不到的实体进行分组。条形图绘制了一组有序的值 - 因此,如果间距有一些有趣的东西,那么它并不是真正的条形图。

但我认为您正在寻找的答案是:

SELECT DATE_FORMAT(dateandtime, '%Y-%m-%d') as ondate
, SUM(IF(Pass='Pass',1,0)) AS passed
, SUM(IF(Pass='Fail',1,0)) AS failed
, SUM(IF(Pass='Fail',1,0))
  /(SUM(IF(pass='Pass',1,0))+SUM(IF(Pass='Fail',1,0))) AS fail_pct
, cases.primaryapp 
FROM casehistory, cases
WHERE DATE_SUB(CURDATE(),INTERVAL 80 DAY) <= dateandtime
AND cases.casenumber = casehistory.casenumber
AND cases.primaryapp = 'Promo'
GROUP BY DATE_FORMAT(dateandtime, '%Y-%m-%d')
ORDER BY 1;

如果您需要 Unix 时间戳,请将以上内容包含在...中。

SELECT UNIX_TIMESTAMP(STR_TO_DATE(CONCAT(ilv.ondate, ' 00:00:00'))) AS tstamp
, passed
, failed
, fail_pct
, primaryapp
FROM (
     ...
) AS ilv

请注意,您仍然会遇到有关 DST 开关的异常情况。

C.

Question is very confused.

Your SQL statement makes no sense - you are grouping by entities not found in the select statement. And a bar chart plots an ordered set of values - so if there's something funny with the spacing then its not really a bar chart.

But I think the answer you are looking for is:

SELECT DATE_FORMAT(dateandtime, '%Y-%m-%d') as ondate
, SUM(IF(Pass='Pass',1,0)) AS passed
, SUM(IF(Pass='Fail',1,0)) AS failed
, SUM(IF(Pass='Fail',1,0))
  /(SUM(IF(pass='Pass',1,0))+SUM(IF(Pass='Fail',1,0))) AS fail_pct
, cases.primaryapp 
FROM casehistory, cases
WHERE DATE_SUB(CURDATE(),INTERVAL 80 DAY) <= dateandtime
AND cases.casenumber = casehistory.casenumber
AND cases.primaryapp = 'Promo'
GROUP BY DATE_FORMAT(dateandtime, '%Y-%m-%d')
ORDER BY 1;

And if you need Unix timestamps, wrap the above in....

SELECT UNIX_TIMESTAMP(STR_TO_DATE(CONCAT(ilv.ondate, ' 00:00:00'))) AS tstamp
, passed
, failed
, fail_pct
, primaryapp
FROM (
     ...
) AS ilv

Note that you'll still get anomolies around DST switches.

C.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文