Storing C# datetime to postgresql TimeStamp

Question

I'm working on an app that stores data in a spreadsheet to a Postgresql database. I'm familiar with C# and .Net but not so well with Postgresql. I'm having trouble storing a DateTime value into a TimeStamp column; I keep getting an error message: Failed to convert parameter value from a DateTime to a Byte[]. Any advice would be appreciated.

string query = "INSERT INTO organizer(organizer_name, contact_name, phone, alt_phone, created_date, last_update) " +
                "VALUES('@name', '@contactname', '@phone', '@altphone', '@created', '@updated')";

            OdbcCommand cmd = new OdbcCommand(query, con);
            cmd.Parameters.Add("@name", OdbcType.VarChar);
            cmd.Parameters["@name"].Value = org.Name;

            cmd.Parameters.Add("@contactname", OdbcType.VarChar);
            cmd.Parameters["@contactname"].Value = org.ContactName;

            cmd.Parameters.Add("@phone", OdbcType.VarChar);
            cmd.Parameters["@phone"].Value = org.Phone;

            cmd.Parameters.Add("@altphone", OdbcType.VarChar);
            cmd.Parameters["@altphone"].Value = org.AltPhone;

            cmd.Parameters.Add("@created", OdbcType.Timestamp).Value = DateTime.Now;

            cmd.Parameters.Add("@updated", OdbcType.Timestamp).Value = DateTime.Now;

            con.Open();
            cmd.ExecuteNonQuery();

Answer 1

I don't have a PostgreSQL db handy to test with, but I believe that you are seeing this because the OdbcType.Timestamp is actually a byte array, not a time and date. From MSDN:

Timestamp: A stream of binary data (SQL_BINARY). This maps to an Array of type Byte.

This is probably because the timestamp datatype, in SQL Server, is

a data type that exposes automatically generated, unique binary numbers within a database. timestamp is generally used as a mechanism for version-stamping table rows.

I would try using OdbcType.DateTime, which seems to map to the concept behind PostgreSQL's timestamp.

EDIT:

Here is a useful post which summarizes the mappings between PostgreSQL and .NET.

Answer 2

You've got a few solutions here...I'm going to assume the organizer table has the created_date and last_update as timestamp fields, correct? The silliest answer is to change those to varchar fields. heh.

2 better answers...I'm assuming this is a formatting error where DateTime.Now doesn't return in the format pgsql wants:

Since you are just giving it the current timestamp

• you can define your table to default these columns to now() and then not pass values to this column, on an insert the table would just populate that with the default of now().

• instead of defining the variable to DateTime.Now and then passing the variable, just send postgres now() and it will populate it in the format it feels right.

And second potential is to format the date into what PG expects as part of the insert statement...I'd need to know what DateTime.Now gives for a value to format it to what pg wants to see. This might be a bit of string manipulation...