SharePoint 2010/2013 Date Comparison in different Time Zones

Working with DateTime in SharePoint is always tricky since it stores values internally in UTC and when we tried to access it in CSOM or SOM in different Time zone we have to be very careful while filtering on Created, Modified Date columns.

Time Zone plays very crucial roles in any Date Time difference calculation. When we do difference in two date times, it must be in same Time Zone offset otherwise the result will never be correct. Here, TimeZoneInfo class plays main role. To get specific time zone details we have to use TimeZoneInfo.FindSystemTimeZoneById method which will take input of Zone Id. All available Time Zone Id’s can be found at MSDN https://msdn.microsoft.com/en-us/library/gg154758.aspx.

To explain this problem lets take a example that we need to find all SharePoint List items which was created in last 5 minutes.

First we will retrieve all Items from a List which is created Today. We will use CAML Query to get these items.

// Store result in temporary Table
DataTable dataResult = new DataTable();
dataResult.Columns.Add("ID");
dataResult.Columns.Add("Title");

//Get List Data
SPWeb sbWeb = SPContext.Current.Site.RootWeb;
SPList list = sbWeb.GetList("MyCustomList");
SPQuery listQuery = new SPQuery();

// Get items for todays date
listQuery.Query = "<Where><Eq><FieldRef Name='Created' /><Value Type='DateTime'><Today /></Value></Eq></Where>";
SPListItemCollection items = list.GetItems(listQuery);

Iterate through each list item and perform Date time conversion.

foreach(SPListItem item in items) {
	if (item[“Title”] != null && item[“ID”] != null) {

		// Get UTC Now Date Time
		DateTime utcNowDate = new DateTime(DateTime.UtcNow.Ticks, DateTimeKind.Utc);

		// Find the Server Time Zone Info which is configured under Central Administration -> Web Application -> General Settings
		TimeZoneInfo timeZoneInfo = TimeZoneInfo.FindSystemTimeZoneById(“Pacific Standard Time”);

		// Convert UTC Now Date Time to server’s Time Zone i.e. into Pacific Standard Time in our example.
		DateTime convertedUtcNowDateTime = TimeZoneInfo.ConvertTimeFromUtc(utcNowDate, timeZoneInfo);

		// Find the Date time difference into TimeSpan
		TimeSpan diffResult = convertedUtcNowDateTime–Convert.ToDateTime(item[“Created”]);

		// Create 5 Minutes Time Span object
		TimeSpan fiveMinutesSpan = new TimeSpan(0, 5, 0);

		// Compare method will return -1 if first time interval is shorter than second and return 0 if both time interval are equal. Read more about this at https://msdn.microsoft.com/en-us/library/system.timespan.compare(v=vs.110).aspx
		int result = TimeSpan.Compare(diffResult, fiveMinutesSpan);

		// If result is 0 Or -1 it means items is created in last five minutes.
		if (a < 1) {
			DataRow row = dataResult.NewRow();
			row["Title"] = item[SPBuiltInFieldId.EncodedAbsUrl].ToString();
			row["ID"] = item[ColumnName.ReportUrl].ToString();
			data.Rows.Add(dr);
		}
	}
}

Resulting DataTable will have the filtered result.