如何从两个列值中减去小时和分钟 [英] how to subtract hours and minutes from two column values

问题描述

大家好，

我有一个表abc，其列和数据为:
员工，小时数，分钟数，小时数，分钟数

Mohd Wasif，6,12,0,27

这里Dhours，Dmins，MHours和MMins是int值.

现在我的问题是我必须从Dhours中减去MHours，从Dmins中减去MMins

但我的看法是负面的，但我必须按小时和分钟获取数据
表示如果Dmins小于MMins，在这种情况下，答案应该是45，而不是-15，并且应该减去小时，意味着5.

我将Sql查询用作

Hi All,

I have a table abc having column and data as :
Employee,Dhours,Dmins,MHours,MMins

Mohd Wasif,6,12,0,27

here Dhours,Dmins,MHours and MMins are int values.

now my problem is this I have to subtract MHours from Dhours and MMins from Dmins

but I am getting in negative but i have to get data as per hours and minutes
means if Dmins is less than MMins like in this case answer should be 45 instead of -15 and hours should be subtracted means 5 .

i used Sql query as

select Employee,Dhours,Dmins,MHours,MMins,(Dhours-MHours)as netHours,(Dmins-MMins) as netMins from abc

请帮帮我

添加了代码块-OriginalGriff [/edit]

Please help me

[edit]Codeblock added - OriginalGriff[/edit]

推荐答案

我认为这是您认为更基本的一点:给定的查询不符合您的描述.
在描述中，您从DHours中减去了MHours；在查询中，您从DHours中减去MMins.

看来您正在尝试计算经过的时间:例如从登录时间和注销时间开始.存储登录/注销的小时和分钟不是实现此目的的好方法.

我认为您真正需要做的是更改存储方式:不要存储小时和分钟来进行以后的数学运算，而是存储日期和时间.然后，当您对它们进行数学运算时，您将获得明智的结果.

如果仅用小时和分钟计算，那么您的数学将变得更加复杂:您必须应对日间变化，例如在2:45进站，在3:15出站"和在2:15进站"在2:45"都产生30分钟，依此类推.

I think this is a bit more fundamental that you think: Your query as given does not do what you describe.
In your description, you subtract MHours from DHours; in your query you subtract MMins from DHours.

It looks like you are trying to work out elapsed time: from a log in time and a log out time for example. Storing the hour and minute of log in/out is not a good way to do that.

I think what you really need to do is change the way you store things: Instead of storing Hours and Minutes for later math, store Date and time instead. Then when you do maths on them, you will get sensible results.

If you keep working with just the hour and the minute, then your math becomes more complex: you have to cope with day change, with "in at 2:45, out at 3:15" and "in at 2:15, out at 2:45" both generating 30 minutes, and so forth.

这篇关于如何从两个列值中减去小时和分钟的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！