按天对 Unix 时间戳进行分组,产生不均匀间隔的组
我正在使用 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 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
问题很困惑。
您的 SQL 语句没有任何意义 - 您正在按 select 语句中找不到的实体进行分组。条形图绘制了一组有序的值 - 因此,如果间距有一些有趣的东西,那么它并不是真正的条形图。
但我认为您正在寻找的答案是:
如果您需要 Unix 时间戳,请将以上内容包含在...中。
请注意,您仍然会遇到有关 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:
And if you need Unix timestamps, wrap the above in....
Note that you'll still get anomolies around DST switches.
C.