我如何比较时间和时间以及日期 [英] How Can I Compare Time In And Time Out And Date

问题描述

我在房间预订，时间严格，每分钟计数。我如何比较我的数据库中的数据？例如：

Im working on room reservation, time strict, that every minutes count.How can i compare data in my database? for example:

    Date			Time In			Time Out 
March 20, 2015			8:00			9:00	           allowed 
March 20, 2015			8:30			9:30	          not allowed
March 20, 2015          9:01            10:00              allowed

i有一个名为meeting的数据库，字段_date，_timein，_timeout请帮忙，我不知道怎么说，但希望你能帮帮我..谢谢

i have a database that name meeting, field _date,_timein,_timeout please help, i dont know how to say it but hope you can help me..Thanks

推荐答案

你没有给出很多信息。所以我假设一些东西：



- 你的数据库中有你现有的和已经验证的预订。



- 在进行新预订时，您要检查它是否与任何现有预订不重叠，即使是触摸时间也不会 允许（08:00 - 09:00<> 09:00 - 10:00）。



我们打电话给田地数据库中的预订表 DbFrom 和 DbUntil 。以及要验证的预订 newFrom 和 newUntil 的新字段。



然后这个查询（-schema）会告诉你现有的预约是否与新的重叠：

You didn't give a whole lot of information. So I'm assuming some stuff:

- You have your existing and already validated reservations in your database.

- When taking a new reservation you want to check if it doesn't overlap with any of the existing reservations and even touching times would not be allowed (08:00 - 09:00 <> 09:00 - 10:00).

Let's call the fields of the reservation-table in the database DbFrom and DbUntil. And the fields of the new, to be validated reservation newFrom and newUntil .

Then this query (-schema) would tell you if there is an existing reservation that does overlap with the new one:

SELECT <columns> | 1
FROM   Reservation
WHERE  newFrom <= DbUntil AND newUntil >= DbFrom

如果您有兴趣知道它是哪一个，选择< columns> ，否则只需SELECT 1 。

If you're interested to know which one it is, SELECT the <columns> , otherwise just SELECT 1 .

Imports System.Drawing.Drawing2D
Imports System.Data.OleDb
Imports System.Data
Imports System.IO
Public Class MeetingR
    Dim cn As New System.Data.OleDb.OleDbConnection
    Dim cmd As System.Data.OleDb.OleDbCommand
    Dim myDA As System.Data.OleDb.OleDbDataAdapter
    Dim myDataSet As System.Data.DataSet
    Const Convert As Double = Math.PI / 180
    Const SecRadius As Double = 62
    Const MinRadius As Double = 58
    Const HrRadius As Double = 53
    Const CentreX As Integer = 80
    Const CentreY As Integer = 110
    Const alarmRadius = 60
    Dim AlarmAngle As Double
    Dim SecAngle As Double
    Dim MinAngle As Double
    Dim HrAngle As Double
    Dim hrs, min As Double
    Dim AlarmX As Single = 80
    Dim AlarmY As Single = 45
    Dim SecX As Single = 80
    Dim SecY As Single = 45
    Dim MinX As Single = 80
    Dim MinY As Single = 45
    Dim HrX As Single = 80
    Dim HrY As Single = 45
    Dim Timestring As String
    Dim StartPoint(60) As PointF
    Dim EndPoint(60) As PointF
    Dim NumberPoint(12) As PointF
    Dim NumberFont As New Font("Times New Roman", 11, FontStyle.Bold)
    Dim DigitalFont As New Font("Arial", 25, FontStyle.Bold)
    Dim FormType As Boolean
    Dim AlarmFlag As Boolean
    Dim AnalogueFlag As Boolean = True
    Dim tTip As New ToolTip
    Dim pnlAlarm As Panel
    Dim pnlAlarm2 As Panel
    Dim WithEvents btnAlarm As New Button
    Dim WithEvents TBAlarm As New TrackBar
    Dim WithEvents btnAlarm2 As New Button
    Dim WithEvents TBAlarm2 As New TrackBar
    Dim lblTB As New Label
    Dim lblAlarm As New Label
    Dim lblTB2 As New Label
    Dim lblAlarm2 As New Label
    Dim WithEvents btnClockType As New Button
    Dim WithEvents tmrClock As New Timer
    Dim Clock As New Bitmap(160, 160)
    Dim gr As Graphics = Graphics.FromImage(Clock)
    'Load Form calling and initialize
    Private Sub MeetingR1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        DateTimePicker1.Format = DateTimePickerFormat.Short
        DateTimePicker1.Value = Date.Today
        btnCreate.Visible = False
        DoubleBuffered = True
        Me.CenterToScreen()
        Calculate_Increments()
        Create_Alarm_Panel()
        Create_Alarm_Panel2()
        tmrClock.Interval = 995
        tmrClock.Start()
        Call DataSetFill()

        Try
            If FrmLogin.CBformState.Text = "User" Then
                Dim cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\VB\AdvanceLoginForm\AdvanceLoginForm\bin\Debug\BackUp\RoomRe.accdb;Persist Security Info=False;")
                If cn.State = ConnectionState.Open Then
                    cn.Close()
                End If
                cn.Open()

                Dim dr1 As OleDbDataReader
                Dim com As New OleDbCommand
                com.CommandText = "select [UserID],[UserName] from UserInfo where UserID = @UName"

                ' UserName
                Dim UName As OleDbParameter = New OleDbParameter("@UName", OleDbType.VarWChar, 30)
                UName.Value = UCase(FrmLogin.txtUserName.Text.ToString())
                com.Parameters.Add(UName)
                com.Connection = cn

                dr1 = com.ExecuteReader
                If dr1.Read Then
                    UserName.Text = UCase(dr1("UserName").ToString())

                    cn.Close()
                    Return
                End If

            End If

        Catch ex As Exception
            MsgBox(ex.Message(), MsgBoxStyle.Critical, "Error...")
        End Try
    End Sub
    Sub DataSetFill()
        Try
            cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\VB\AdvanceLoginForm\AdvanceLoginForm\bin\Debug\BackUp\RoomRe.accdb;Persist Security Info=False;"

            If cn.State - System.Data.ConnectionState.Closed Then
                cn.Open()
            End If
            cmd = New System.Data.OleDb.OleDbCommand("SELECT (Username)as [Name],(DMY)as [Date], (t1)as [Time In], (t2)as [Time Out] FROM Meeting  ", cn)
            myDA = New System.Data.OleDb.OleDbDataAdapter(cmd)
            myDataSet = New System.Data.DataSet
            'DataGridView1.Sort(DataGridView1.Columns(2), System.ComponentModel.ListSortDirection.Ascending)
            myDA.Fill(myDataSet, "Meeting")

            DataGridView1.DataSource = myDataSet.Tables("Meeting").DefaultView
            cn.Close()
        Catch ex As Exception
            MsgBox(ex.Message(), MsgBoxStyle.Critical, "Error")
        End Try
    End Sub
    Private Function RequiredEntry() As Boolean
        If DateTimePicker1.Text = "" Or TextBox1.Text = "" Or TextBox2.Text = "" Then
            MsgBox("Please enter all information....", MsgBoxStyle.Critical, "Attention")
            Return True
            Exit Function
        End If
    End Function
    Private Function TimeEr() As Boolean
        If (DateTimePicker1.Text < DateTime.Today) Then
            MsgBox("Date Error!", vbCritical)
            Return True
            Exit Function
        End If
    End Function
    Private Function datedate() As Boolean

    End Function
    Sub Create_Alarm_Panel()
        pnlAlarm = New Panel
        Me.Controls.Add(pnlAlarm)
        With pnlAlarm
            .Size = New Size(100, 150)
            .Location = New Point(160, 40)
            .BorderStyle = BorderStyle.Fixed3D
            .BackColor = Color.SteelBlue
        End With
        pnlAlarm.Controls.Add(lblAlarm)
        With lblAlarm
            .Size = New Size(80, 30)
            .Font = New Font("Arial", 9)
            .Location = New Point(10, 5)
            .Text = "Move Pointer" & vbCrLf & "Set time out!"
            .BackColor = Color.LightBlue
            .BorderStyle = BorderStyle.Fixed3D
        End With
        pnlAlarm.Controls.Add(btnAlarm)
        With btnAlarm
            .Size = New Size(60, 40)
            .Location = New Point(20, 100)
            .BackColor = Color.LightBlue
            .FlatStyle = FlatStyle.Popup
            .Text = "Time Out"
            .Font = New Font("Arial", 9)
        End With
        pnlAlarm.Controls.Add(TBAlarm)
        With TBAlarm
            .Value = 0
            .Maximum = 1439
            .Minimum = 0
            .SmallChange = 1
            .LargeChange = 1
            .Location = New Point(10, 45)
            .Size = New Size(80, 25)
            .BackColor = Color.Beige
        End With
        pnlAlarm.Controls.Add(lblTB)
        With lblTB
            .Font = New Font("Arial", 14, FontStyle.Bold)
            .Text = "00:00"
            .Size = New Size(65, 20)
            .Location = New Point(18, 67)
            .BackColor = Color.Beige
            .BorderStyle = BorderStyle.Fixed3D
            .BringToFront()
        End With
    End Sub
    Sub Create_Alarm_Panel2()
        pnlAlarm2 = New Panel
        Me.Controls.Add(pnlAlarm2)
        With pnlAlarm2
            .Size = New Size(100, 150)
            .Location = New Point(22, 40)
            .BorderStyle = BorderStyle.Fixed3D
            .BackColor = Color.SteelBlue
        End With
        pnlAlarm2.Controls.Add(lblAlarm2)
        With lblAlarm2
            .Size = New Size(80, 30)
            .Font = New Font("Arial", 9)
            .Location = New Point(10, 5)
            .Text = "Move Pointer" & vbCrLf & "Set time in!"
            .BackColor = Color.LightBlue
            .BorderStyle = BorderStyle.Fixed3D
        End With
        pnlAlarm2.Controls.Add(btnAlarm2)
        With btnAlarm2
            .Size = New Size(60, 40)
            .Location = New Point(20, 100)
            .BackColor = Color.LightBlue
            .FlatStyle = FlatStyle.Popup
            .Text = "Time In"
            .Font = New Font("Arial", 9)
        End With
        pnlAlarm2.Controls.Add(TBAlarm2)
        With TBAlarm2
            .Value = 0
            .Maximum = 1439
            .Minimum = 0
            .SmallChange = 1
            .LargeChange = 1
            .Location = New Point(10, 45)
            .Size = New Size(80, 25)
            .BackColor = Color.Beige
        End With
        pnlAlarm2.Controls.Add(lblTB2)
        With lblTB2
            .Font = New Font("Arial", 14, FontStyle.Bold)
            .Text = "00:00"
            .Size = New Size(65, 20)
            .Location = New Point(18, 67)
            .BackColor = Color.Beige
            .BorderStyle = BorderStyle.Fixed3D
            .BringToFront()
        End With
    End Sub
    Sub Calculate_Increments() 'around clock face circumference
        Dim X, Y As Integer
        Dim count As Integer = 0
        For I As Integer = 1 To 60
            If I Mod 5 = 0 Then 'Calculate number positions at every 12th increment
                count += 1
                'Get the X,Y co-ordinates of the number positions
                'Derived from the formula: X = rCosθ & Y = rSinθ  
                X = CentreX + CInt(61 * Math.Cos((90 - I * 6) * Convert))
                Y = CentreY - CInt(61 * Math.Sin((90 - I * 6) * Convert))
                NumberPoint(count).X = X - 6
                NumberPoint(count).Y = Y + -38
            End If 'Calculate increments
            'Calculate Start Point
            'Derived from the formula: X = rCosθ & Y = rSinθ  
            X = CentreX + CInt(68 * Math.Cos((90 - I * 6) * Convert))
            Y = CentreY - CInt(68 * Math.Sin((90 - I * 6) * Convert))
            StartPoint(I) = New PointF(X, Y - 30)
            'Calculate End Point
            'Derived from the formula: X = rCosθ & Y = rSinθ  
            X = CentreX + CInt(75 * Math.Cos((90 - I * 6) * Convert))
            Y = CentreY - CInt(75 * Math.Sin((90 - I * 6) * Convert))
            EndPoint(I) = New PointF(X, Y - 30)
        Next
    End Sub
    Private Sub TBAlarm_Scroll(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TBAlarm.Scroll
        Dim value As Integer
        value = CInt(TBAlarm.Value)
        hrs = CInt(Fix(value / 60))
        min = CInt(TBAlarm.Value - (hrs * 60))
        lblTB.Text = Format(hrs, "00") & ":" & Format(min, "00")
        AlarmAngle = (hrs + min / 60) * 30
        'Get the X,Y co-ordinates of the end point of the Alarm indicator
        'Derived from the formula: X = rCosθ & Y = rSinθ  
        AlarmX = CentreX + CInt(alarmRadius * Math.Cos((90 - AlarmAngle) * Convert))
        AlarmY = CentreY - CInt(alarmRadius * Math.Sin((90 - AlarmAngle) * Convert))
    End Sub
    Private Sub TBAlarm_Scroll2(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TBAlarm2.Scroll
        Dim value As Integer
        value = CInt(TBAlarm2.Value)
        hrs = CInt(Fix(value / 60))
        min = CInt(TBAlarm2.Value - (hrs * 60))
        lblTB2.Text = Format(hrs, "00") & ":" & Format(min, "00")
        AlarmAngle = (hrs + min / 60) * 30
        'Get the X,Y co-ordinates of the end point of the Alarm indicator
        'Derived from the formula: X = rCosθ & Y = rSinθ  
        AlarmX = CentreX + CInt(alarmRadius * Math.Cos((90 - AlarmAngle) * Convert))
        AlarmY = CentreY - CInt(alarmRadius * Math.Sin((90 - AlarmAngle) * Convert))
    End Sub
    'Saving data and closing form
    Private Sub btnMeeting_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
        If RequiredEntry() = True Then
            Return
        End If

        If TimeEr() = True Then
            Return
        End If

        Try
            Dim cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\VB\AdvanceLoginForm\AdvanceLoginForm\bin\Debug\BackUp\RoomRe.accdb;Persist Security Info=False;")
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
            cn.Open()




            Dim sSQL As String = "insert into Meeting(UserName,DMY,t1,t2) values(@name,@d1,@d2,@d3)"
            Dim cmd As OleDbCommand = New OleDbCommand(sSQL, cn)
            'Username
            Dim nm As OleDbParameter = New OleDbParameter("@name", OleDbType.VarWChar, 255)
            nm.Value = UserName.Text.ToString()
            cmd.Parameters.Add(nm)
            'DAte
            Dim dati As OleDbParameter = New OleDbParameter("@d1", OleDbType.VarWChar, 255)
            dati.Value = DateTimePicker1.Text.ToString()
            cmd.Parameters.Add(dati)
            'time in
            Dim timein As OleDbParameter = New OleDbParameter("@d2", OleDbType.VarWChar, 255)
            timein.Value = TextBox1.Text.ToString()
            cmd.Parameters.Add(timein)
            ' timeout
            Dim timeout As OleDbParameter = New OleDbParameter("@d3", OleDbType.VarWChar, 255)
            timeout.Value = TextBox2.Text.ToString()
            cmd.Parameters.Add(timeout)

            If cmd.ExecuteNonQuery() Then
                cn.Close()
                MsgBox("Reservation for room successfully... ", MsgBoxStyle.Information, "Record Saved")

            Else
                MsgBox("Reservation failed... ", MsgBoxStyle.Critical, "Reservation failed")
                Return

            End If

        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString(), "Data Error")
            Exit Sub
        End Try

    End Sub
    Sub btnAlarmClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAlarm.Click

        If (lblTB.Text < TextBox1.Text Or lblTB.Text = lblTB2.Text) Then
            MsgBox("Time Error!")
        Else
            TextBox2.Text = lblTB.Text
        End If
    End Sub
    Sub btnAlarmClick2(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAlarm2.Click
        TextBox1.Text = lblTB2.Text
    End Sub
    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        Me.Dispose()
        FrmMain.Show()

    End Sub
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCheck.Click
        btnCreate.Visible = True
    End Sub
    Private Sub MeetingR_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        Me.Dispose()
        FrmMain.Show()
    End Sub
End Class

这篇关于我如何比较时间和时间以及日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！