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();

Community · Accepted Answer · 2017-05-23 12:27:39Z

2

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.

edited May 23, 2017 at 12:27

CommunityBot

11 silver badge

answered Dec 29, 2011 at 19:36

dsolimano

9,0463 gold badges52 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JediusX Over a year ago

That solves that error, but produces another error - ERROR: invalid input syntax for type timestamp: "@created"; Error while executing the query.

Twelfth · Accepted Answer · 2011-12-29 20:04:36Z

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...

Collectives™ on Stack Overflow

Storing C# datetime to postgresql TimeStamp

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related