我们知道数据库里的datediff函数可以判断两个时间的时间差,在做统计时,常常会统计:今天的记录,本周的记录,本月的记录,三个月内的记录,本年度记录等等。
用datediff函数时的写法大致如下:
case "datetimespan":
switch (keyword.ToLower())
{
case "all":
break;
case "today":
filter = filter + "AND datediff(day,chattime,getdate())=0";
break;
case "week":
filter = filter + "AND datediff(week,chattime,getdate())=0";
break;
case "month":
filter = filter + "AND datediff(month,chattime,getdate())=0";
break;
case "threemonth":
filter = filter + "AND datediff(month,chattime,getdate())<=2";
break;
}
break;
如果想对时间段做的更为人性化或更精确,还是自己拆分时间段比较好。比如统计时间到当前时间。
using System;
using System.Collections.Generic;
using System.Globalization;
namespace 日期分割
{
class Program
{
static void Main()
{
Dictionary<string, string> timeRangeDic = new Dictionary<string, string>();
Console.WriteLine("==================[一天内时间段(到当前时间)分割]====================");
DateTime todayStart = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd 00:00:00"));
for (int hour = 1; hour <= DateTime.Now.Hour; hour++)
{
string key = string.Format("{0}-{1}", todayStart.AddHours(hour - 1).ToString("HH时"),
todayStart.AddHours(hour).ToString("HH时"));
if (!timeRangeDic.ContainsKey(key))
{
timeRangeDic.Add(key,
todayStart.AddHours(hour - 1).ToString("yyyy-MM-dd HH:00:00") + " 到 " +
todayStart.AddHours(hour).ToString("yyyy-MM-dd HH:00:00"));
}
}
foreach (KeyValuePair<string, string> pair in timeRangeDic)
{
Console.WriteLine(pair.Key + " " + pair.Value);
}
Console.WriteLine();
Console.WriteLine("==================[最近一周内(到当前时间)时间段分割]================");
timeRangeDic.Clear();
DateTime weekStart =
DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd 00:00:00")).AddDays(-(int)(DateTime.Now.DayOfWeek));
for (int day = 0; day < Convert.ToInt32(DateTime.Now.DayOfWeek); day++)
{
string key = string.Format("{0}-{1}", GetWeek(day), GetWeek(day + 1));
if (!timeRangeDic.ContainsKey(key))
{
if (day == Convert.ToInt32(DateTime.Now.DayOfWeek) - 1)
{
timeRangeDic.Add(key,
weekStart.AddDays(day).ToString("yyyy-MM-dd HH:00:00") + " 到 " +
DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));//当前时间
}
else
{
timeRangeDic.Add(key,
weekStart.AddDays(day).ToString("yyyy-MM-dd HH:00:00") + " 到 " +
weekStart.AddDays(day + 1).ToString("yyyy-MM-dd HH:00:00"));
}
}
}
foreach (KeyValuePair<string, string> pair in timeRangeDic)
{
Console.WriteLine(pair.Key + " " + pair.Value);
}
Console.WriteLine();
Console.WriteLine("==================[最近一月内(到当前时间)时间段分割]==============");
timeRangeDic.Clear();
DateTime monthStart = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-01 00:00:00"));
for (int day = 1; day < DateTime.Now.Date.Day; day++)
{
string key = string.Format("{0}日-{1}日", day.ToString(CultureInfo.InvariantCulture).PadLeft(2, '0'),
(day + 1).ToString(CultureInfo.InvariantCulture).PadLeft(2, '0'));
if (!timeRangeDic.ContainsKey(key))
{
if (day == DateTime.Now.Date.Day - 1)
{
timeRangeDic.Add(key,
monthStart.AddDays(day - 1).ToString("yyyy-MM-dd HH:00:00") + " 到 " +
DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
}
else
{
timeRangeDic.Add(key,
monthStart.AddDays(day - 1).ToString("yyyy-MM-dd HH:00:00") + " 到 " +
monthStart.AddDays(day).ToString("yyyy-MM-dd HH:00:00"));
}
}
}
foreach (KeyValuePair<string, string> pair in timeRangeDic)
{
Console.WriteLine(pair.Key + " " + pair.Value);
}
Console.WriteLine();
Console.WriteLine("==================[最近三个月内(到当前时间)时间段分割]==============");
timeRangeDic.Clear();
DateTime currMonthStart = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-01 00:00:00"));
for (int month = 2; month >= 0; month--)
{
string key = string.Format("{0}-{1}",
currMonthStart.AddMonths(-month - 1).ToString("yyyy年MM月"),
currMonthStart.AddMonths(-month).ToString("yyyy年MM月"));
if (!timeRangeDic.ContainsKey(key))
{
if (month == 0)
{
timeRangeDic.Add(key,
currMonthStart.AddMonths(-month - 1)
.ToString("yyyy-MM-dd HH:00:00") +
" 到 " +
DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));//当前时间
}
else
{
timeRangeDic.Add(key,
currMonthStart.AddMonths(-month - 1)
.ToString("yyyy-MM-dd HH:00:00") +
" 到 " +
currMonthStart.AddMonths(-month).ToString("yyyy-MM-dd HH:00:00"));
}
}
}
foreach (KeyValuePair<string, string> pair in timeRangeDic)
{
Console.WriteLine(pair.Key + " " + pair.Value);
}
Console.WriteLine();
Console.WriteLine("==================[本年内(到当前时间)时间段分割]===================");
timeRangeDic.Clear();
DateTime yearStart = DateTime.Parse(DateTime.Now.ToString("yyyy-01-01 00:00:00"));
for (int month = 1; month < DateTime.Now.Date.Month; month++)
{
string key = string.Format("{0}月-{1}月",
month.ToString(CultureInfo.InvariantCulture).PadLeft(2, '0'),
(month + 1).ToString(CultureInfo.InvariantCulture).PadLeft(2, '0'));
if (!timeRangeDic.ContainsKey(key))
{
if (month == DateTime.Now.Date.Month - 1)
{
timeRangeDic.Add(key,
yearStart.AddMonths(month - 1).ToString("yyyy-MM-dd HH:00:00") +
" 到 " +
DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
}
else
{
timeRangeDic.Add(key,
yearStart.AddMonths(month - 1).ToString("yyyy-MM-dd HH:00:00") +
" 到 " +
yearStart.AddMonths(month).ToString("yyyy-MM-dd HH:00:00"));
}
}
}
foreach (KeyValuePair<string, string> pair in timeRangeDic)
{
Console.WriteLine(pair.Key + " " + pair.Value);
}
Console.Read();
}
public static string GetWeek(int current)
{
string[] weekdays = { "周日", "周一", "周二", "周三", "周四", "周五", "周六" };
string week = current < weekdays.Length ? weekdays[current] : "";
return week;
}
}
}
输出结果: