特定格式的平均时间和显示（Coldfusion）

Edit: Thanks to barnyr I have the average in seconds, so now I just need to convert it to the correct format. Unfortunately the Coldfusion functions noted by Peter only take a datetime object, so I'm back to being stumped by the conversion.

编辑:感谢barnyr我有几秒钟的平均值,所以现在我只需要将其转换为正确的格式。不幸的是,Peter注意到的Coldfusion函数只占用了一个日期时间对象,所以我又回到了转换的困境。

I have a two-fold question. I need to generate the average time it took Y users to complete a task, and then display the result in a specific format: x Years x Months x Weeks x Days x Hours x Minutes x Seconds.

我有一个双重问题。我需要生成Y用户完成任务所需的平均时间,然后以特定格式显示结果:x年x个月x周x天x小时x分钟x秒。

My backend database is SQL Server 2008, and I'm running the queries through Coldfusion 10. Both columns are datetime fields.

我的后端数据库是SQL Server 2008,我正在通过Coldfusion 10运行查询。这两列都是日期时间字段。

The query is (similar to):

查询是(类似于):

SELECT    Started_Date, Completed_Date
FROM      tbl_progress
WHERE     0=0

I then need to iterate through the query results, get the difference between the Completed_Date and Started_Date (ie. duration), average the results, and display as noted above.

然后我需要遍历查询结果,获得Completed_Date和Started_Date之间的差异(即持续时间),平均结果,并如上所述显示。

From another thread on * I got the following code, but it will only work for the days/hours/minutes/seconds sections of the required format.

从*上的另一个线程我得到以下代码,但它只适用于所需格式的天/小时/分钟/秒部分。

<cfloop query="complete_time">
    <cfset completed = ParseDateTime(complete_time.Completed_Date) />
    <cfset started = ParseDateTime(complete_time.Started_Date) />
    <cfset difference = (completed - started) /> 
    <cfset fixed_diff = Fix(difference) />  
    <cfset days = fixed_diff /> 
    <cfset hours = TimeFormat(difference, "H") /> 
    <cfset minutes = TimeFormat(difference, "m") />  
    <cfset seconds = TimeFormat(difference, "s")/ >
</cfloop>

I need to know how/where to do the averaging, and how to get the years/months/weeks values for my required format. Date math is definitely not my best subject! Thanks.

我需要知道如何/在何处进行平均,以及如何获得所需格式的年/月/周值。日期数学绝对不是我最好的科目!谢谢。

2 个解决方案

#1

I would do the work in the database if you possibly can:

如果可能的话,我会在数据库中完成工作:

SELECT    Started_Date, 
          Completed_Date, 
          DATEDIFF(Second, Started_Date, Completed_Date) AS sec_diff
FROM      tbl_progress

You can then compute the seconds into minutes, hours etc in ColdFusion.

然后,您可以在ColdFusion中将秒数计算为分钟,小时等。

As you work through the results, you could sum up the sec_diff values. Then divide them by the number of row in your result set.

在处理结果时,您可以总结sec_diff值。然后将它们除以结果集中的行数。

You could also average the results in a query. Tave a look at SQL's AVG() function.

您还可以在查询中对结果进行平均。看看SQL的AVG()函数。

I'm not near a SQL server, but you may be able to use the following:

我不在SQL服务器附近,但您可以使用以下内容:

SELECT    Started_Date, 
          Completed_Date, 
          DATEDIFF(Second, Started_Date, Completed_Date) AS sec_diff,
          AVG(DATEDIFF(Second, Started_Date, Completed_Date)) AS average_task_time
FROM      tbl_progress

#2

I suggest using the datediff function in sql server to get the number of seconds. Then in ColdFusion you don't use timeformat, you do math. Timeformat() takes a datetime object and returns a string.

我建议在sql server中使用datediff函数来获取秒数。然后在ColdFusion中你不使用timeformat,你做数学。 Timeformat()接受一个datetime对象并返回一个字符串。

#1