Home > Visual Basic Language > round datetime to a specific value

round datetime to a specific value

I have datetime values that are to begrouped into various intervals. For example, 10, 20 30minute intervals. The problem is that I need to round start andend datetimes to easily divisible values. For example, for 10 minutes intervals:

2007-07-17T07:23:00 needs to be roundeddown to 2007-07-17T07:20:00

2007-07-17T23:58:00 needs to be rounded up to 2007-07-18T00:00:00

Generically, the rounded up datetime needs to be conceptually:

MyIntervalValueModulus MyDateTimeValue = 0

Can anyone point me in the right direction?

rwbta Wednesday, July 18, 2007 2:51 AM

I believe you'd have to write a function similar to the following - the logic is mentioned in the comments:

Code Snippet

Public Function RoundDate(ByVal dt As Date, ByVal IntervalMinutes As Integer) As Date

'Get the total number of minutes represented by the date

'by multipling out the ticks

Dim min As Double = (dt.Ticks * (10 ^ -7)) / 60

'Divide by the minute interval to round to

min = min / IntervalMinutes

'Round off the value - this could be changed to

'Floor or Ceiling as required

min = Math.Round(min)

'Multiply back by the minute interval

min = min * IntervalMinutes

'Calculate the ticks representing the offest total minutes

Dim ticks As Long = (min * 60) * (10 ^ 7)

'Return a the rounded date from the calculated ticks

Return New Date(ticks)

End Function

Hope that helps!

Reed Kimble Wednesday, July 18, 2007 4:33 AM

I believe you'd have to write a function similar to the following - the logic is mentioned in the comments:

Code Snippet

Public Function RoundDate(ByVal dt As Date, ByVal IntervalMinutes As Integer) As Date

'Get the total number of minutes represented by the date

'by multipling out the ticks

Dim min As Double = (dt.Ticks * (10 ^ -7)) / 60

'Divide by the minute interval to round to

min = min / IntervalMinutes

'Round off the value - this could be changed to

'Floor or Ceiling as required

min = Math.Round(min)

'Multiply back by the minute interval

min = min * IntervalMinutes

'Calculate the ticks representing the offest total minutes

Dim ticks As Long = (min * 60) * (10 ^ 7)

'Return a the rounded date from the calculated ticks

Return New Date(ticks)

End Function

Hope that helps!

Reed Kimble Wednesday, July 18, 2007 4:33 AM

Hi,

I have this kind of output so far for rounding to 10 mins.

As you see from the first section, i have rounded down where minutes <=4 ( less than half way )

I have roundedto10 minutes if between 5 and 14 minutes.

The code to do all this is at the bottom of this lot.

Just add a large richtextbox to a FORM to see the output.

I have created a function called

RoundMyDateTime in this code example.

See the blue and white line that looks

like this

below, for an example of how to use it.

Regards,

S_DS

18/07/2007 00:0:00 18/07/2007 00:00:00

18/07/2007 00:1:00 18/07/2007 00:00:00

18/07/2007 00:2:00 18/07/2007 00:00:00

18/07/2007 00:3:00 18/07/2007 00:00:00

18/07/2007 00:4:00 18/07/2007 00:00:00

18/07/2007 00:5:00 18/07/2007 00:10:00

18/07/2007 00:6:00 18/07/2007 00:10:00

18/07/2007 00:7:00 18/07/2007 00:10:00

18/07/2007 00:8:00 18/07/2007 00:10:00

18/07/2007 00:9:00 18/07/2007 00:10:00

18/07/2007 00:10:00 18/07/2007 00:10:00

18/07/2007 00:11:00 18/07/2007 00:10:00

18/07/2007 00:12:00 18/07/2007 00:10:00

18/07/2007 00:13:00 18/07/2007 00:10:00

18/07/2007 00:14:00 18/07/2007 00:10:00

18/07/2007 00:15:00 18/07/2007 00:20:00

18/07/2007 00:16:00 18/07/2007 00:20:00

18/07/2007 00:17:00 18/07/2007 00:20:00

18/07/2007 00:18:00 18/07/2007 00:20:00

18/07/2007 00:19:00 18/07/2007 00:20:00

18/07/2007 00:20:00 18/07/2007 00:20:00

18/07/2007 00:21:00 18/07/2007 00:20:00

18/07/2007 00:22:00 18/07/2007 00:20:00

18/07/2007 00:23:00 18/07/2007 00:20:00

18/07/2007 00:24:00 18/07/2007 00:20:00

18/07/2007 00:25:00 18/07/2007 00:30:00

18/07/2007 00:26:00 18/07/2007 00:30:00

18/07/2007 00:27:00 18/07/2007 00:30:00

18/07/2007 00:28:00 18/07/2007 00:30:00

18/07/2007 00:29:00 18/07/2007 00:30:00

18/07/2007 00:30:00 18/07/2007 00:30:00

18/07/2007 00:31:00 18/07/2007 00:30:00

18/07/2007 00:32:00 18/07/2007 00:30:00

18/07/2007 00:33:00 18/07/2007 00:30:00

18/07/2007 00:34:00 18/07/2007 00:30:00

18/07/2007 00:35:00 18/07/2007 00:40:00

18/07/2007 00:36:00 18/07/2007 00:40:00

18/07/2007 00:37:00 18/07/2007 00:40:00

18/07/2007 00:38:00 18/07/2007 00:40:00

18/07/2007 00:39:00 18/07/2007 00:40:00

18/07/2007 00:40:00 18/07/2007 00:40:00

18/07/2007 00:41:00 18/07/2007 00:40:00

18/07/2007 00:42:00 18/07/2007 00:40:00

18/07/2007 00:43:00 18/07/2007 00:40:00

18/07/2007 00:44:00 18/07/2007 00:40:00

18/07/2007 00:45:00 18/07/2007 00:50:00

18/07/2007 00:46:00 18/07/2007 00:50:00

18/07/2007 00:47:00 18/07/2007 00:50:00

18/07/2007 00:48:00 18/07/2007 00:50:00

18/07/2007 00:49:00 18/07/2007 00:50:00

18/07/2007 00:50:00 18/07/2007 00:50:00

18/07/2007 00:51:00 18/07/2007 00:50:00

18/07/2007 00:52:00 18/07/2007 00:50:00

18/07/2007 00:53:00 18/07/2007 00:50:00

18/07/2007 00:54:00 18/07/2007 00:50:00

18/07/2007 00:55:00 18/07/2007 01:00:00

18/07/2007 00:56:00 18/07/2007 01:00:00

18/07/2007 00:57:00 18/07/2007 01:00:00

18/07/2007 00:58:00 18/07/2007 01:00:00

18/07/2007 00:59:00 18/07/2007 01:00:00

Public Class Form1

Dim myHour As Integer

Dim myMinutes As Integer

Dim mySeconds As Integer

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim fullDateTimeString As String

Dim roundedDate As Date

Dim dateToRound As Date

Me.Show()

For min As Integer = 0 To 59

fullDateTimeString = "18/07/2007 00:" & min.ToString & ":00"

dateToRound = CDate(fullDateTimeString)

roundedDate = RoundMyDateTime(dateToRound, 10)

RichTextBox1.AppendText(fullDateTimeString & vbTab & vbTab)

RichTextBox1.AppendText(" " & roundedDate.ToShortDateString)

RichTextBox1.AppendText(" " & roundedDate.ToLongTimeString & vbNewLine)

End Sub

Public Function RoundMyDateTime(ByVal dt1 As Date, ByVal roundInterval As Integer) As Date

myMinutes = dt1.Minute

mySeconds = dt1.Second

'Round the minutes based on the seconds.

Select Case mySeconds

Case Is < 30

mySeconds = 0

'If greater than 30 add 1 to the mins.

Case Is > 30

mySeconds = 0

myMinutes += 1

End Select

Dim myInterval As Integer = roundInterval

Dim remainder As Double = 0

remainder = CDbl(myMinutes) / CDbl(myInterval) - Int((CDbl(myMinutes) / CDbl(myInterval)))

Select Case remainder

'Round down if less than half way.

Case Is < 0.5

myMinutes = myInterval * CInt(Int((CDbl(myMinutes) / CDbl(myInterval))))

'Round up if half way or more.

Case Is >= 0.5

myMinutes = myInterval * CInt(Int((CDbl(myMinutes) / CDbl(myInterval)) + 1))

End Select

'Round the hour if mins>=58

If myMinutes >= 58 Then

myMinutes = 0

myHour = dt1.Hour + 1

If dt1.Hour = 23 Then myHour = 0

End If

Dim dateString As String = ""

dateString &= dt1.Day.ToString

dateString &= "/" & dt1.Month.ToString

dateString &= "/" & dt1.Year.ToString

dateString &= " " & myHour.ToString

dateString &= ":" & myMinutes.ToString

dateString &= ":" & mySeconds.ToString

Return CDate(dateString)

End Function

End Class

John Anthony Oliver Wednesday, July 18, 2007 5:05 AM

rkimble wrote:

I believe you'd have to write a function similar to the following - the logic is mentioned in the comments:

Code Snippet

Public Function RoundDate(ByVal dt As Date, ByVal IntervalMinutes As Integer) As Date

'Get the total number of minutes represented by the date

'by multipling out the ticks

Dim min As Double = (dt.Ticks * (10 ^ -7)) / 60

'Divide by the minute interval to round to

min = min / IntervalMinutes

'Round off the value - this could be changed to

'Floor or Ceiling as required

min = Math.Round(min)

'Multiply back by the minute interval

min = min * IntervalMinutes

'Calculate the ticks representing the offest total minutes

Dim ticks As Long = (min * 60) * (10 ^ 7)

'Return a the rounded date from the calculated ticks

Return New Date(ticks)

End Function

Hope that helps!

Hi rkimble,

I like the 6 effective lines of your function.

Very well thought out.

I'm glad some can think in terms of datetime ticks.

Regards,

S_DS

John Anthony Oliver Wednesday, July 18, 2007 5:16 AM

Hi SDS,

There's an issue with your code - it will only work until the day would change... That is, if your hour variable increments past 23, which it would after 11:54 PM, it will throw an exception when you call CDate. You would have to test for that, and increment the day - and then test the month, and then test the year. This is what makes in impractical to only work with individual components of the date.

Converting the date to Ticks and then rounding that single value allows the day, month, and year to be taken care of automatically.

Don't feel bad! Your example could be extrapolated out to work beyond one day, but as you can see, calculating via ticks only takes 5 or so lines of code, so its far more efficient. But your post adds value to the thread by demonstrating the difference between a direct and a derived approach. Figuring out the derived approach to a solution often results in simplicity, but there are times when the direct approach is best!

Thanks for sharing!

Reed Kimble Wednesday, July 18, 2007 5:33 AM

Hi rkimble,

Thanks for pointing that out but I couldn't get it to throw an exception.

I have made the highlighted change to the code which should prevent that happening anyway.

I still prefer your 6 lines of code though, oh well, mine was just a different approach.

Regards,

S_DS

John Anthony Oliver Wednesday, July 18, 2007 12:01 PM

Works great! Thanks!

rwbta Wednesday, July 18, 2007 1:25 PM

Hi SDS,

Hmmm... that should throw an exception - at least it does for me... I wonder if you're regional settings affect it? I did overlook another issue that regional settings do affect: the date string format - building the string as dd/MM will also throw an exception for me. In the following code, only the first conversion works on my computer, the other two throw an InvalidCastException:

Code Snippet

Dim dt As Date

dt = CDate("7/21/2007 23:15:30") 'Standard US Format (MM/dd/yyyy)

dt = CDate("21/7/2007 23:15:30") 'Standard Euro Format (dd/MM/yyyy)

dt = CDate("7/21/2007 24:15:30") 'Hour greater than 23

Also, the highlighted change in your code does handle the day rolling over, but what about the month? Now you have to test to see if the current day is the last day of the current month and increment the month if it is (which is obviously apain because you first have to determine how many days are in the current month this year!). And then you have to do the same for the year.

Like I said, your code can be made to work - it will just take the additional checks mentioned above and rebuilding the final dateusing theNew Date(year, month, day, hour, minute, second) overload, so that the regional settings are irrelevant.

Reed Kimble Wednesday, July 18, 2007 3:16 PM

SDS in the highlighted portion of the code above why would you round the hour if the minutes was equal to 58. If you are rounding to the nearest minute wouldn't 58 round to 59 and 59 round to the next hour?

PEng1 Wednesday, July 18, 2007 6:02 PM

PEng1 wrote:

Hi PEng1,

I made an assumption ( bad I know ) that the OP may only want to round to a minimum of 5 minute intervals.

Anyway user rkimble has now answered this OP question, so I'll leave this thread alone now.

Regards,

S_DS

John Anthony Oliver Sunday, July 22, 2007 5:19 PM