/// <summary>
/// 获取 Time 类型的 SQL 片断
/// </summary>
/// <param name="value">值</param>
/// <param name="dbType">数据类型</param>
/// <param name="scale">小数位</param>
/// <returns></returns>
protected override string GetSqlValueByTime(object value, object dbType, int?scale)
{
// the range is '-838:59:59.000000' to '838:59:59.000000' new TimeSpan(-34, -22, -59, -59)~new TimeSpan(34, 22, 59, 59);
// https://dev.mysql.com/doc/refman/8.0/en/time.html
TimeSpan ts = (TimeSpan)value;
int hours = (int)ts.TotalHours;
// 默认精度为7
string format = @"mm\:ss\.ffffff";
if (DbTypeUtils.IsTime(dbType))
{
string s = string.Empty;
if (scale != null && scale.Value > 0)
{
s = string.Empty.PadLeft(scale.Value > 6 ? 6 : scale.Value, 'f');
}
if (!string.IsNullOrEmpty(s))
{
format = string.Format(@"mm\:ss\.{0}", s);
}
}
string result = ts.ToString(format);
result = string.Format("{0}:{1}", hours, result);
result = this.EscapeQuote(result, false, false);
return(result);
}
// 获取 Time 类型的 SQL 片断
protected override string GetSqlValueByTime(object value, object dbType, int?scale)
{
// https://docs.oracle.com/en/database/oracle/oracle-database/12.2/nlspg/datetime-data-types-and-time-zone-support.html#GUID-FD8C41B7-8CDC-4D02-8E6B-5250416BC17D
TimeSpan ts = (TimeSpan)value;
// 默认精度为6
string format = @"hh\:mm\:ss\.ffffff";
if (DbTypeUtils.IsTime(dbType))
{
string s = string.Empty;
if (scale != null && scale.Value > 0)
{
s = string.Empty.PadLeft(scale.Value > 7 ? 7 : scale.Value, 'f');
}
if (!string.IsNullOrEmpty(s))
{
format = string.Format(@"hh\:mm\:ss\.{0}", s);
}
}
string result = ts.ToString(format);
result = string.Format("{0} {1}", ts.Days, result);
result = this.EscapeQuote(result, false, false);
result = string.Format("TO_DSINTERVAL({0})", result);
return(result);
}
/// <summary>
/// 获取 Time 类型的 SQL 片断
/// </summary>
/// <param name="value">值</param>
/// <param name="dbType">数据类型</param>
/// <param name="scale">小数位</param>
/// <returns></returns>
protected override string GetSqlValueByTime(object value, object dbType, int?scale)
{
// SQLSERVER 的Time类型范围：00:00:00.0000000 到 23:59:59.9999999
// https://docs.microsoft.com/zh-cn/sql/t-sql/data-types/time-transact-sql?view=sql-server-2017
// 默认精度为7
string format = @"hh\:mm\:ss\.fffffff";
if (DbTypeUtils.IsTime(dbType))
{
string s = string.Empty;
if (scale != null && scale.Value > 0)
{
s = string.Empty.PadLeft(scale.Value > 7 ? 7 : scale.Value, 'f');
}
if (!string.IsNullOrEmpty(s))
{
format = string.Format(@"hh\:mm\:ss\.{0}", s);
}
}
string result = this.EscapeQuote(((TimeSpan)value).ToString(format), false, false);
return(result);
}
// 获取 Time 类型的 SQL 片断
protected override string GetSqlValueByTime(object value, object dbType, int?precision)
{
// 默认精度为7
string format = @"hh\:mm\:ss\.fffffff";
if (DbTypeUtils.IsTime(dbType))
{
string pad = string.Empty;
if (precision != null && precision.Value > 0)
{
pad = "f".PadLeft(precision.Value > 7 ? 7 : precision.Value, 'f');
}
if (!string.IsNullOrEmpty(pad))
{
format = string.Format(@"hh\:mm\:ss\.{0}", pad);
}
}
string result = this.EscapeQuote(((TimeSpan)value).ToString(format), false, false);
return(result);
}
// 获取 Time 类型的 SQL 片断
protected override string GetSqlValueByTime(object value, object dbType, int?scale)
{
// 默认精度6
string format = @"hh\:mm\:ss\.ffffff";
if (DbTypeUtils.IsTime(dbType))
{
string s = string.Empty;
if (scale != null && scale.Value > 0)
{
s = string.Empty.PadLeft(scale.Value > 6 ? 6 : scale.Value, 'f');
}
if (!string.IsNullOrEmpty(s))
{
format = string.Format(@"hh\:mm\:ss\.{0}", s);
}
}
string date = ((TimeSpan)value).ToString(format);
string result = string.Format("'{0}'::TIME", date);
return(result);
}
/// <summary>
/// 访问 ToString 方法
/// </summary>
/// <param name="node">即将访问的表达式</param>
protected override Expression VisitToStringImpl(Expression node)
{
// => a.ID.ToString()
// 字符串不进行转换
if (node == null || node.Type == typeof(string))
{
return(_visitor.Visit(node));
}
// 其它类型转字符串
bool isDate =
node.Type == typeof(TimeSpan) ||
node.Type == typeof(TimeSpan?) ||
node.Type == typeof(DateTime) ||
node.Type == typeof(DateTime?) ||
node.Type == typeof(DateTimeOffset) ||
node.Type == typeof(DateTimeOffset?);
if (isDate)
{
_builder.Append("TO_CHAR(");
_visitor.Visit(node);
string format = string.Empty;
ColumnAttribute c = _visitedMark.Current != null?TypeUtils.GetColumnAttribute(_visitedMark.Current.Member, _visitedMark.Current.ReflectedType) : null;
if (c != null && DbTypeUtils.IsTime(c.DbType))
{
format = "hh24:mi:ss.us";
}
else if (c != null && DbTypeUtils.IsDate(c.DbType))
{
format = "yyyy-mm-dd";
}
else if (c != null && (DbTypeUtils.IsDateTime(c.DbType) || DbTypeUtils.IsDateTime2(c.DbType)))
{
format = "yyyy-mm-dd hh24:mi:ss.us";
}
else if (c != null && DbTypeUtils.IsDateTimeOffset(c.DbType))
{
format = "yyyy-mm-dd hh24:mi:ss.us TZH:TZM";
}
// 没有显式指定数据类型，则根据表达式的类型来判断
if (string.IsNullOrEmpty(format))
{
if (node.Type == typeof(TimeSpan) || node.Type == typeof(TimeSpan?))
{
format = "hh24:mi:ss.us";
}
else if (node.Type == typeof(DateTime) || node.Type == typeof(DateTime?))
{
format = "yyyy-mm-dd hh24:mi:ss.us";
}
else if (node.Type == typeof(DateTimeOffset) || node.Type == typeof(DateTimeOffset?))
{
format = "yyyy-mm-dd hh24:mi:ss.us TZH:TZM";
}
}
if (!string.IsNullOrEmpty(format))
{
_builder.Append(",'");
_builder.Append(format);
_builder.Append("'");
}
_builder.Append(')');
}
else if (node.Type == typeof(byte[]))
{
_builder.Append("ENCODE(");
_visitor.Visit(node);
_builder.Append(",'hex')");
}
else
{
_builder.Append("CAST(");
_visitor.Visit(node);
_builder.Append(" AS VARCHAR)");
}
return(node);
}