如何减去access sql查询中的时间两列

发布于 2024-11-04 19:42:42 字数 611 浏览 0 评论 0原文

我在其中一张表中有两列：Arrival_Time 和Leaving_Time。两者都是时间字段，格式如下：HH:MM:SS。

我想从 Arrival_Time 中减去 Leaving_Time，例如：

到达时间：02:00:00
出发时间：02:45:00 = 45 分钟。

我尝试了这个查询：

SELECT  (Jobs.Leaving_time) - (Jobs.Arrival_Time) AS Time_Difference
FROM Technicians,Jobs, Tech_Allocation
WHERE Jobs.Job_ID=Tech_Allocation.Job_ID     
  AND Tech_Allocation.Technician_ID=Technicians.Technician_ID
ORDER BY  (Jobs.Leaving_time) - (Jobs.Arrival_Time) ASC;

查询运行，但减法的结果以小数形式显示，而不是我希望的分钟数。

谢谢

原文

I have two columns in one of the tables : Arrival_Time and Leaving_Time.
Both are time fields which are formatted like this: HH:MM:SS.

I want to subtract Leaving_Time from Arrival_Time, for example:

Arrival time: 02:00:00
Leaving_time: 02:45:00 = 45 minutes.

I tried this query :

SELECT  (Jobs.Leaving_time) - (Jobs.Arrival_Time) AS Time_Difference
FROM Technicians,Jobs, Tech_Allocation
WHERE Jobs.Job_ID=Tech_Allocation.Job_ID     
  AND Tech_Allocation.Technician_ID=Technicians.Technician_ID
ORDER BY  (Jobs.Leaving_time) - (Jobs.Arrival_Time) ASC;

The query runs but the results of the subtraction are shown in decimals not as minutes as I want them to be.

thank you

分享到QQ

分享到微博

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

发布评论

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

岁月蹉跎了容颜 2024-11-11 19:42:43

在您的问题中，您说您希望持续时间显示为分钟。然而，在评论中，您说您希望结果显示为小时：分钟：秒。

使用 Format() 函数将浮点值显示为小时:分钟:秒。

SELECT Format(j.Leaving_time - j.Arrival_Time, "hh:nn:ss") AS Time_Difference
FROM Technicians AS t, Jobs AS j, Tech_Allocation AS ta
WHERE j.Job_ID = ta.Job_ID     
  AND ta.Technician_ID = t.Technician_ID
ORDER BY 1 ASC;

请注意，此方法仅对小于 24 小时的 Time_Differences 有效。

编辑：

我在 ORDER BY 中使用 1 来引用字段列表中的第一项。比把字段表达式写在ORDER BY中方便多了。
您实际上并不需要在此处的 ORDER BY 中使用 ASC。
我将使用 Access 的查询设计器通过 INNER JOIN 表达式连接表，而不是旧式的 WHERE 方法。
为表分配简短且有意义的别名可以使 SQL 更加简洁且更易于人们理解。

In your question, you said you want the time duration displayed as minutes. However in a comment, you said you want the result displayed as Hour:Minutes:Seconds.

Use the Format() function to display your float value as Hour:Minutes:Seconds.

SELECT Format(j.Leaving_time - j.Arrival_Time, "hh:nn:ss") AS Time_Difference
FROM Technicians AS t, Jobs AS j, Tech_Allocation AS ta
WHERE j.Job_ID = ta.Job_ID     
  AND ta.Technician_ID = t.Technician_ID
ORDER BY 1 ASC;

Beware, this approach is only valid for Time_Differences less than 24 hours.

Edit:

I used 1 in the ORDER BY to refer to the first item in the field list. It's more convenient than writing the field expression in the ORDER BY.
You don't really need ASC in the ORDER BY here.
I would use Access' query designer to connect up the tables with INNER JOIN expressions, rather than the old-style WHERE approach.
Assigning short meaningful ALIASes for the tables can make the SQL more concise and easier for humans to comprehend.

回复收藏 0 原文