如何使用C＃获取两个DateTime之间的总时差？

As you can see below I declared the queries and DB operations required for the application. In this class I tried to calculate total time with TimeSpan but was unsuccessful.

如下所示,我声明了应用程序所需的查询和数据库操作。在本课程中,我尝试使用TimeSpan计算总时间,但未成功。

public class Operations
{
    public Dbconection db = new Dbconection();
    public Informations info = new Informations();

    DateTime Vrijeme;
    DateTime myDate1;

    public int insertEmp(Informations info)
    {
        DateTime Time = DateTime.Now;
        DateTime myDate1 = new DateTime(Time.Year, Time.Month, 
            Time.Day, Time.Hour, 10, 00);
        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "INSERT INTO empRegister" +
            "(Operater,Artikl,Kal,Bazz,Tempa,Time,Status)" +
            "VALUES ('" + info.operater + "','" + info.artikl + "','" + info.kal + "','" + info.bazz + "','" + info.tempa + "', @time, 'online')";
        cmd.Parameters.AddWithValue("@time", myDate1);
        return db.ExeNonQuery(cmd);
    }

    public DataTable Logout1(Informations info)
    {
        DateTime Logout = DateTime.Now;
        DateTime myDate2 = new DateTime(Logout.Year, Logout.Month, Logout.Day, Logout.Hour, 30, 00);

        TimeSpan ts = myDate2.Subtract(myDate1);
        TimeSpan result = new TimeSpan(ts.Hours, ts.Minutes, ts.Milliseconds);

        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.Text;

        cmd.CommandText = "update empRegister set Logout = @logout, Total = @THETIME, Status = 'logout' where Operater ='" + info.operater + "' and Status = 'online'";
        cmd.Parameters.AddWithValue("@logout", myDate2);
        cmd.Parameters.AddWithValue("@THETIME", SqlDbType.Time);
        cmd.Parameters["@THETIME"].Value = result;

        return db.ExeReader(cmd);
    }
}

With insert button I want use Datetime.Now and with update button I want to store the current time and total time of two DateTime with Timespan. I am using a SQL DB.

使用插入按钮我想使用Datetime.Now和更新按钮我想存储两个DateTime与Timespan的当前时间和总时间。我正在使用SQL DB。

2 个解决方案

#1

I'm not a big fan of storing the time data. However, you can do it in the SQL query assuming you are storing it as a time column:

我不是存储时间数据的忠实粉丝。但是,您可以在SQL查询中执行此操作,假设您将其存储为时间列:

cmd.CommandText = "update empRegister set Logout = @logout, Total = CONVERT(time(7),DATEADD(SECOND, 
                        DATEDIFF(SECOND, Time, @logout),
                        0)), Status = 'logout' where Operater = @Operater and Status = 'online'";
cmd.Parameters.AddWithValue("@logout", myDate2);
cmd.Parameters.AddWithValue("@Operater", info.operater);

None of the TimeSpan code is necessary.

没有TimeSpan代码是必需的。

Also, I made the operator a parameter. It's misspelled in your question and in my query for consistency.

另外,我将操作符作为参数。它在您的问题和我的查询中是拼写错误的。

Here's a SQL Fiddle example of a time column use:

这是时间列使用的SQL Fiddle示例:

create table y
(
  x time
  );

INSERT INTO Y SELECT 
CONVERT(time(7),DATEADD(SECOND, 
                        DATEDIFF(SECOND, GETDATE()-7.5, GETDATE()),
                        0));


SELECT * FROM Y

Note that the time column is limited to 24 hours.

请注意,时间列限制为24小时。

#2

In the empRegister table I have stored 3 Data Type with names (Time,Logout and Total). I do not know where I'm wrong and every help is welcome.

在empRegister表中,我存储了3个数据类型,其中包含名称(Time,Logout和Total)。我不知道我哪里错了,欢迎各种帮助。

#1