// 获取 DateTimeOffset 类型的 SQL 片断 protected override string GetSqlValueByDateTimeOffset(object value, object dbType, int?scale) { // 默认精度为6 string format = "yyyy-MM-dd HH:mm:ss.ffffff"; if (DbTypeUtils.IsDateTimeOffset(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("yyyy-MM-dd HH:mm:ss.{0}", s); } } string myDateTime = ((DateTimeOffset)value).DateTime.ToString(format); string myOffset = ((DateTimeOffset)value).Offset.ToString(@"hh\:mm"); myOffset = string.Format("{0}{1}", ((DateTimeOffset)value).Offset < TimeSpan.Zero ? '-' : '+', myOffset); string result = string.Format("TO_TIMESTAMP_TZ('{0} {1}','yyyy-mm-dd hh24:mi:ss.ff tzh:tzm')", myDateTime, myOffset); return(result); }
/// <summary> /// 获取 DateTimeOffset 类型的 SQL 片断 /// </summary> /// <param name="value">值</param> /// <param name="dbType">数据类型</param> /// <param name="scale">小数位</param> /// <returns></returns> protected override string GetSqlValueByDateTimeOffset(object value, object dbType, int?scale) { // 默认精度为7 string format = "yyyy-MM-dd HH:mm:ss.fffffff"; if (DbTypeUtils.IsDateTimeOffset(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("yyyy-MM-dd HH:mm:ss.{0}", s); } } string myDateTime = this.EscapeQuote(((DateTimeOffset)value).DateTime.ToString(format), false, false); string myOffset = ((DateTimeOffset)value).Offset.ToString(@"hh\:mm"); myOffset = string.Format("{0}{1}", ((DateTimeOffset)value).Offset < TimeSpan.Zero ? '-' : '+', myOffset); myOffset = this.EscapeQuote(myOffset, false, false); string result = string.Format("TODATETIMEOFFSET({0},{1})", myDateTime, myOffset); return(result); }
// 获取 DateTimeOffset 类型的 SQL 片断 protected override string GetSqlValueByDateTimeOffset(object value, object dbType, int?scale) { // 默认精度6 string format = "yyyy-MM-dd HH:mm:ss.ffffff"; if (DbTypeUtils.IsDateTimeOffset(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("yyyy-MM-dd HH:mm:ss.{0}", s); } } string myDateTime = ((DateTimeOffset)value).DateTime.ToString(format); string myOffset = ((DateTimeOffset)value).Offset.ToString(@"hh\:mm"); myOffset = string.Format("{0}{1}", ((DateTimeOffset)value).Offset < TimeSpan.Zero ? '-' : '+', myOffset); string result = string.Format("'{0}{1}'::TIMESTAMPTZ ", myDateTime, myOffset); return(result); // Npgsql 的显示都是以本地时区显示的?### }
// 获取 DateTimeOffset 类型的 SQL 片断 protected override string GetSqlValueByDateTimeOffset(object value, object dbType, int?precision) { // 默认精度为7 string format = "yyyy-MM-dd HH:mm:ss.fffffff"; if (DbTypeUtils.IsDateTimeOffset(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("yyyy-MM-dd HH:mm:ss.{0}", pad); } } string date = this.EscapeQuote(((DateTimeOffset)value).DateTime.ToString(format), false, false); string span = ((DateTimeOffset)value).Offset.ToString(@"hh\:mm"); span = string.Format("{0}{1}", ((DateTimeOffset)value).Offset.Hours >= 0 ? '+' : '-', span); span = this.EscapeQuote(span, false, false); string result = string.Format("TODATETIMEOFFSET({0},{1})", date, span); return(result); }
/// <summary> /// 获取 DateTimeOffset 类型的 SQL 片断 /// </summary> /// <param name="value">值</param> /// <param name="dbType">数据类型</param> /// <param name="scale">小数位</param> /// <returns></returns> protected override string GetSqlValueByDateTimeOffset(object value, object dbType, int?scale = null) { DbTypeUtils.IsDateTimeOffset(dbType); return(null); }
/// <summary> /// 访问 ToString 方法 /// </summary> protected override Expression VisitToStringImpl(Expression node) { // => a.ID.ToString() // 字符串不进行转换 if (node == null || node.Type == typeof(string)) { return(_visitor.Visit(node)); } ColumnAttribute column = null; bool isUnicode = _provider.DbValue.IsUnicode(_visitedMark.Current, out column); bool isBytes = node.Type == typeof(byte[]); bool isDate = node.Type == typeof(DateTime) || node.Type == typeof(DateTime?) || node.Type == typeof(TimeSpan) || node.Type == typeof(TimeSpan?) || node.Type == typeof(DateTimeOffset) || node.Type == typeof(DateTimeOffset?); if (!isBytes) { if (isUnicode) { _builder.Append("TO_NCHAR("); } else { _builder.Append("TO_CHAR("); } } // 其它类型转字符串 if (isDate) { _visitor.Visit(node); string format = string.Empty; ColumnAttribute c = _provider.DbValue.GetColumnAttribute(_visitedMark.Current); 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.ff"; } else if (c != null && DbTypeUtils.IsDateTimeOffset(c.DbType)) { format = "yyyy-mm-dd hh24:mi:ss.ff tzh:tzm"; } // 没有显式指定数据类型,则根据表达式的类型来判断 if (string.IsNullOrEmpty(format)) { if (node.Type == typeof(DateTime) || node.Type == typeof(DateTime?)) { format = "yyyy-mm-dd hh24:mi:ss.ff"; } else if (node.Type == typeof(DateTimeOffset) || node.Type == typeof(DateTimeOffset?)) { format = "yyyy-mm-dd hh24:mi:ss.ff tzh:tzm"; } } if (!string.IsNullOrEmpty(format)) { _builder.Append(",'"); _builder.Append(format); _builder.Append("'"); } } else if (isBytes) { _builder.Append("RTRIM(DBMS_LOB.SUBSTR("); _visitor.Visit(node); _builder.Append(')'); } else if (node.Type == typeof(Guid)) { _builder.Append("REGEXP_REPLACE(REGEXP_REPLACE("); _visitor.Visit(node); _builder.Append(@",'(.{8})(.{4})(.{4})(.{4})(.{12})', '\1-\2-\3-\4-\5'),'(.{2})(.{2})(.{2})(.{2}).(.{2})(.{2}).(.{2})(.{2})(.{18})','\4\3\2\1-\6\5-\8\7\9')"); } else { _visitor.Visit(node); } _builder.Append(')'); return(node); }
/// <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); }