I need help with an issue that I have run into while implementing interval columns in PostgreSQL. I am using a client application that is based on .NET.
If create a Timespan object like so:
TimeSpan startBalance = new TimeSpan(100, 0, 0); //100 hours
and assign this value to the Poco property like so:
//the poco property was generated as a string - not a Timespan - not sure why
Poco.Property = startBalance.ToString()
The PetaPoco insert creates the following interval value in the PostgreSQL database:
"4 days 00:57:36"
When I try to retrieve the record containing the interval I previously saved - a com exception is generated.
A few things of to note...
The Database file generation process (POCO creation) has created a string property to represent the PostgreSQL interval column, not a Timespan property. The Timespan equaling 100 hour (4 days and 4 hours) is inserted as 4 days 57 minutes 36 seconds. Retrieve fails with this error "Object must implement IConvertible." If I update the interval field to NULL then the retrieve works. It appears something in the datatype mapping isn't quite right. My understanding of this is that PostgreSQL interval maps to NpgsqlDbType interval and this should map to .NET TimeSpan. Perhaps the the issue is that PetaPoco is treating the datatype as a string, not sure. Need a guru. ;-)
