I can save and load from the db properly. Using this typeconverter:
[Description("Converter with as core type System.Timespan, for mapping a field with a .NET type Timespan onto an interval database field")]
public class NpgsqlIntervalConverter : TypeConverter
{
public NpgsqlIntervalConverter()
{
}
public override bool CanConvertFrom(ITypeDescriptorContext context, Type sourceType)
{
return ((sourceType==typeof(NpgsqlInterval)) || (sourceType==typeof(TimeSpan)));
}
public override bool CanConvertTo(ITypeDescriptorContext context, Type destinationType)
{
return ((destinationType == typeof(NpgsqlInterval)) || (destinationType == typeof(TimeSpan)));
}
public override object ConvertFrom(ITypeDescriptorContext context, System.Globalization.CultureInfo culture, object value)
{
if(value == null)
{
return null;
}
if(value.GetType() == typeof(NpgsqlInterval))
{
return ((NpgsqlInterval)value).Time;
}
return value;
}
public override object ConvertTo(ITypeDescriptorContext context, System.Globalization.CultureInfo culture, object value, Type destinationType)
{
if(value==null)
{
throw new ArgumentNullException("value", "Value can't be null");
}
// value is always a Timespan
return new NpgsqlInterval((TimeSpan)value);
}
public override object CreateInstance(ITypeDescriptorContext context, System.Collections.IDictionary propertyValues)
{
return TimeSpan.Zero;
}
}
and this testcode:
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
TestTabEntity t = new TestTabEntity();
t.IntervalCol = new TimeSpan(2, 4, 0);
Debug.Assert(adapter.SaveEntity(t, true));
Debug.Assert(t.Id > 0);
TestTabEntity toLoad = new TestTabEntity(t.Id);
Debug.Assert(adapter.FetchEntity(toLoad));
Console.WriteLine(toLoad.Fields[(int)TestTabFieldIndex.IntervalCol].CurrentValue.GetType().FullName);
Debug.Assert(t.IntervalCol.Equals(toLoad.IntervalCol));
}
The trace sees the interval as a binary blob, i.e. the DbType property of the parameter is set to 'object', which is ok, the provider knows it's a NpgsqlInterval object.
When I return 'value' instead of a new NpgsqlInterval object (so I return the Timespan) it also works. Could you try the typeconverter above for me please?
Please do realize that the 'Interval' type in postgresql can span several days. Timespan can't. So if you want to store several days of time in the interval typed field, you will get overflow errors. In that case, you might want to consider a startdate/enddate approach.