将PersianCalendar另存为表中的字符串，而不是DateTime类型

Question

我有一个使用PersianCalender的应用程序。如果我把日期保存为波斯格式的字符串("1399/01/01"(，那么查询它就很容易了。但我不确定其表现和其他后果。

最佳实践是什么？我应该避免将日期保存为字符串，而只将它们保存为DateTime类型，并在每次需要时进行转换吗？有更好的解决方案吗？

Answer 1

没有真正的最佳实践。您需要在Calculation Cost和Storage Cost之间进行权衡，这取决于您希望如何处理日期时间值以及在哪里对它们执行计算。据我所知，有几种方法是很多人使用的：

1. 将波斯日期另存为nvarchar(10)仅用于演示，并将格里高利等效值保存在日期时间字段中，并对日期时间字段执行日期时间计算
2. 将波斯日期保存为nvarchar(10)，并在C#代码中创建一个自定义的PersianDateTimeType，它可以将此字符串值强制转换为PersianDateTimeType，并非常容易地对其执行日期时间操作。我在这个答案的末尾包含了PersianDateTime类型的代码
3. 您也可以在SQL Server中创建用户定义类型，并将您的值存储为此自定义类型
4. 将波斯日期保存为nvarchar(10)，并在SQL server中创建自定义函数来进行nvarchar到datetime的转换

这是代码PersianDateTimeType，您可以像使用C#内置DatetimeType:一样使用它

public enum PersianDateTimeFormat
{
Date = 0,
DateTime = 1,
LongDate = 2,
LongDateLongTime = 3,
FullDate = 4,
FullDateLongTime = 5,
FullDateFullTime = 6,
DateShortTime = 7,
ShortDateShortTime = 8,
LongDateFullTime = 9
}
public enum PersianDateTimeMode
{
System,
PersianTimeZoneInfo,
UtcOffset
}
public class PersianDateTime
{
private readonly static PersianCalendar _persianCalendar = new PersianCalendar();
private readonly static string[] _dayNames = new string[] { "شنبه", "یکشنبه", "دوشنبه", "سه شنبه", "چهارشنبه", "پنج شنبه", "جمعه" };
private readonly static string[] _monthNames = new string[] { "فروردین", "اردیبهشت", "خرداد", "تیر", "مرداد", "شهریور", "مهر", "آبان", "آذر", "دی", "بهمن", "اسفند" };
public static string AM = "ق.ظ";
public static string PM = "ب.ظ";
public static PersianDateTimeMode Mode = PersianDateTimeMode.UtcOffset;
public static TimeSpan DaylightSavingTimeStart = TimeSpan.FromDays(1);
public static TimeSpan DaylightSavingTimeEnd = TimeSpan.FromDays(185);
public static TimeSpan DaylightSavingTime = TimeSpan.FromHours(1);
public static TimeSpan OffsetFromUtc = new TimeSpan(3, 30, 0);
public static TimeZoneInfo PersianTimeZoneInfo = TimeZoneInfo.FindSystemTimeZoneById("Iran Standard Time");
public static TimeSpan operator -(PersianDateTime d1, PersianDateTime d2)
{
return d1.ToDateTime() - d2.ToDateTime();
}
public static bool operator >(PersianDateTime d1, PersianDateTime d2)
{
return d1.ToDateTime() > d2.ToDateTime();
}
public static bool operator >=(PersianDateTime d1, PersianDateTime d2)
{
return d1.ToDateTime() >= d2.ToDateTime();
}
public static bool operator <=(PersianDateTime d1, PersianDateTime d2)
{
return d1.ToDateTime() <= d2.ToDateTime();
}

public static PersianDateTime operator -(PersianDateTime d, TimeSpan t)
{
return new PersianDateTime(d.ToDateTime() - t);
}
public static PersianDateTime operator +(PersianDateTime d, TimeSpan t)
{
return new PersianDateTime(d.ToDateTime() + t);
}

public static bool operator <(PersianDateTime d1, PersianDateTime d2)
{
return d1.ToDateTime() < d2.ToDateTime();
}
public static bool operator ==(PersianDateTime d1, PersianDateTime d2)
{
if (object.ReferenceEquals(d1, null))
{
return object.ReferenceEquals(d2, null);
}
if (object.ReferenceEquals(d2, null))
{
return false;
}
return d1.ToDateTime() == d2.ToDateTime();
}

public static bool operator !=(PersianDateTime d1, PersianDateTime d2)
{
return !(d1 == d2);
}
public static string GetMonthName(int month)
{
return _monthNames[month + 1];
}

public static string GetDayName(int day)
{
return _dayNames[day];
}
public static bool IsLeapYear(int year)
{
return _persianCalendar.IsLeapYear(year);
}

public static int GetDaysInYear(int year)
{
return _persianCalendar.GetDaysInYear(year);
}
public static int GetDaysInMonth(int year, int month)
{
return _persianCalendar.GetDaysInMonth(year, month);
}
public static PersianDateTime Now
{
get
{
switch (Mode)
{
case PersianDateTimeMode.System:
return new PersianDateTime(DateTime.Now);
case PersianDateTimeMode.PersianTimeZoneInfo:
return new PersianDateTime(TimeZoneInfo.ConvertTime(DateTime.Now, PersianTimeZoneInfo));
case PersianDateTimeMode.UtcOffset:
PersianDateTime now = new PersianDateTime(DateTime.UtcNow.Add(OffsetFromUtc));
return now.IsInDaylightSavingTime ? now.Add(DaylightSavingTime) : now;
default:
throw new NotSupportedException(Mode.ToString());
}
}
}

public static PersianDateTime Parse(string persianDate)
{
return Parse(persianDate, "0");
}

public static PersianDateTime Parse(string persianDate, string time)
{
return new PersianDateTime(int.Parse(persianDate.Replace("/", "")), int.Parse(time.Replace(":", "")));
}
private readonly DateTime _dateTime;
public int Year
{
get { return _persianCalendar.GetYear(_dateTime); }
}

public int Month
{
get { return _persianCalendar.GetMonth(_dateTime); }
}

public int Day
{
get { return _persianCalendar.GetDayOfMonth(_dateTime); }
}
public int Hour
{
get { return _dateTime.Hour; }
}

public int Minute
{
get { return _dateTime.Minute; }
}

public int Second
{
get { return _dateTime.Second; }
}
public int Millisecond
{
get { return _dateTime.Millisecond; }
}

public long Ticks
{
get { return _dateTime.Ticks; }
}
private bool IsInDaylightSavingTime
{
get
{
TimeSpan timeOfYear = TimeOfYear;
return timeOfYear > DaylightSavingTimeStart && timeOfYear < DaylightSavingTimeEnd;
}
}

public TimeSpan TimeOfDay
{
get { return _dateTime.TimeOfDay; }
}

public TimeSpan TimeOfYear
{
get { return this - FirstDayOfYear; }
}

public TimeSpan TimeOfMonth
{
get { return this - FirstDayOfMonth; }
}

public TimeSpan TimeOfWeek
{
get { return this - FirstDayOfWeek; }
}

public PersianDateTime(DateTime dateTime)
{
_dateTime = dateTime;
}

public PersianDateTime(int persianDate)
: this(persianDate, 0)
{
}

public PersianDateTime(int persianDate, short time)
: this(persianDate, time * 100)
{
}

public PersianDateTime(int persianDate, int time)
{
int year = persianDate / 10000;
int month = (persianDate / 100) % 100;
int day = persianDate % 100;
int hour = time / 10000;
int minute = (time / 100) % 100;
int second = time % 100;
_dateTime = _persianCalendar.ToDateTime(year, month, day, hour, minute, second, 0);
}

public PersianDateTime(int year, int month, int day)
: this(year, month, day, 0, 0, 0)
{
}
public PersianDateTime(int year, int month, int day, int hour, int minute, int second)
{
_dateTime = _persianCalendar.ToDateTime(year, month, day, hour, minute, second, 0);
}

public bool IsLeapYear()
{
return _persianCalendar.IsLeapYear(Year);
}

public int DaysInYear
{
get { return _persianCalendar.GetDaysInYear(Year); }
}

public int DaysInMonth
{
get { return _persianCalendar.GetDaysInMonth(Year, Month); }
}
public int GetWeekOfYear(CalendarWeekRule rule)
{
return _persianCalendar.GetWeekOfYear(_dateTime, rule, System.DayOfWeek.Saturday);
}
public int DayOfYear
{
get { return _persianCalendar.GetDayOfYear(_dateTime); }
}
public int DayOfWeek
{
get { return ((int)_dateTime.DayOfWeek + 1) % 7; }
}
public string DayName
{
get { return _dayNames[DayOfWeek]; }
}
public string MonthName
{
get { return _monthNames[Month - 1]; }
}

public PersianDateTime Date
{
get { return new PersianDateTime(_dateTime.Date); }
}

public PersianDateTime FirstDayOfYear
{
get { return AddDays(-DayOfYear + 1).Date; }
}
public PersianDateTime LastDayOfYear
{
get { return AddDays(DaysInYear - DayOfYear).Date; }
}
public PersianDateTime FirstDayOfMonth
{
get { return AddDays(-Day + 1).Date; }
}
public PersianDateTime LastDayOfMonth
{
get { return AddDays(DaysInMonth - Day).Date; }
}
public PersianDateTime FirstDayOfWeek
{
get { return AddDays(-DayOfWeek).Date; }
}
public PersianDateTime LastDayOfWeek
{
get { return AddDays(6 - DayOfWeek).Date; }
}

public PersianDateTime AddSeconds(double value)
{
return new PersianDateTime(_dateTime.AddSeconds(value));
}

public PersianDateTime AddMinutes(double value)
{
return new PersianDateTime(_dateTime.AddMinutes(value));
}

public PersianDateTime AddHours(double value)
{
return new PersianDateTime(_dateTime.AddHours(value));
}

public PersianDateTime AddYears(int value)
{
var newYear = Year + value;
var daysInNewMonth = PersianDateTime.GetDaysInMonth(newYear, Month);
var newDay = daysInNewMonth < Day ? daysInNewMonth : Day;
return new PersianDateTime(Year + value, Month, Day).Add(TimeOfDay);
}

public PersianDateTime AddMonths(int value)
{
var months = Month + value;
var newYear = Year + (months > 0 ? (months - 1) / 12 : months / 12 - 1);
var newMonth = months > 0 ? (months - 1) % 12 + 1 : months % 12 + 12;
var daysInNewMonth = PersianDateTime.GetDaysInMonth(newYear, newMonth);
var newDay = daysInNewMonth < Day ? daysInNewMonth : Day;
return new PersianDateTime(newYear, newMonth, newDay).Add(TimeOfDay);
}

public PersianDateTime AddDays(double value)
{
return new PersianDateTime(_dateTime.AddDays(value));
}

public PersianDateTime Add(TimeSpan value)
{
return new PersianDateTime(_dateTime.Add(value));
}
public DateTime ToDateTime()
{
return _dateTime;
}

public int ToInt()
{
return int.Parse(Year.ToString() + Month.ToString().PadLeft(2, '0') + Day.ToString().PadLeft(2, '0'));
}
public override string ToString()
{
return ToString(PersianDateTimeFormat.DateTime);
}
public string ToString(string format)
{
string towDigitYear = (Year % 100).ToString();
string month = Month.ToString();
string day = Day.ToString();
string fullHour = Hour.ToString();
string hour = (Hour % 12 == 0 ? 12 : Hour % 12).ToString();
string minute = Minute.ToString();
string second = Second.ToString();
string dayPart = Hour >= 12 ? PM : AM;
return format.Replace("yyyy", Year.ToString())
.Replace("yy", towDigitYear.PadLeft(2, '0'))
.Replace("y", towDigitYear)
.Replace("MMMM", MonthName)
.Replace("MM", month.PadLeft(2, '0'))
.Replace("M", month)
.Replace("dddd", DayName)
.Replace("ddd", DayName[0].ToString())
.Replace("dd", day.PadLeft(2, '0'))
.Replace("d", day)
.Replace("HH", fullHour.PadLeft(2, '0'))
.Replace("H", fullHour.ToString())
.Replace("hh", hour.PadLeft(2, '0'))
.Replace("h", hour.ToString())
.Replace("mm", minute.PadLeft(2, '0'))
.Replace("m", minute.ToString())
.Replace("ss", second.PadLeft(2, '0'))
.Replace("s", second)
.Replace("tt", dayPart)
.Replace('t', dayPart[0]);
}
public string ToString(PersianDateTimeFormat format)
{
switch (format)
{
case PersianDateTimeFormat.Date:
return Year.ToString() + "/" + Month.ToString().PadLeft(2, '0') + "/" + Day.ToString().PadLeft(2, '0');
case PersianDateTimeFormat.DateTime:
return ToString(PersianDateTimeFormat.Date) + " " + TimeOfDay.ToHHMMSS();
case PersianDateTimeFormat.DateShortTime:
return ToString(PersianDateTimeFormat.Date) + " " + TimeOfDay.ToHHMM();
case PersianDateTimeFormat.LongDate:
return DayName + " " + Day + " " + MonthName;
case PersianDateTimeFormat.LongDateFullTime:
return DayName + " " + Day + " " + MonthName + " ساعت " + TimeOfDay.ToHHMMSS();
case PersianDateTimeFormat.LongDateLongTime:
return DayName + " " + Day + " " + MonthName + " ساعت " + TimeOfDay.ToHHMM();
case PersianDateTimeFormat.ShortDateShortTime:
return Day.ToString() + " " + MonthName + " " + TimeOfDay.ToHHMM();
case PersianDateTimeFormat.FullDate:
return DayName + " " + Day + " " + MonthName + " " + Year;
case PersianDateTimeFormat.FullDateLongTime:
return DayName + " " + Day + " " + MonthName + " " + Year + " ساعت " + TimeOfDay.ToHHMM();
case PersianDateTimeFormat.FullDateFullTime:
return DayName + " " + Day + " " + MonthName + " " + Year + " ساعت " + TimeOfDay.ToHHMMSS();
default:
throw new NotImplementedException(format.ToString());
}
}
public override int GetHashCode()
{
return _dateTime.GetHashCode();
}

public override bool Equals(object value)
{
return Equals(value as PersianDateTime);
}
public bool Equals(PersianDateTime value)
{
if (object.ReferenceEquals(value, null))
{
return false;
}
return _dateTime.Equals(value._dateTime);
}
}