PostgreSQL: 42883 Operator does not exist: timestamp without time zone

Question

I am using C# (Npgsql 3.2.4.1) and PostgreSQL (9.6.5) and pgAdmin 3.

My table is in PostgreSQL:

create table Data
(                                        
   idref integer NOT NULL,
   val text,
   dt timestamp without time zone NOT NULL,
   constraint PK_Data_idRef_Dt primary key (idRef, Dt),   
   constraint FK_Data_Ref foreign key(idRef) references Ref(Id)                                    
);

When i run function (C#):

static void Main(string[] args)
    {
        var connString = "Server = 0.0.0.0; Port = 5432; Userid = user; Password = password; CommandTimeout=300; Timeout = 300; Database = database";
        using (var conn = new NpgsqlConnection(connString))
        {
            NpgsqlCommand cmd = new NpgsqlCommand(@"select Val, Dt from Data where idRef = @Id and Dt >= @Dt and Dt <= @Dt order by Dt asc;", conn);

            NpgsqlParameter parameterId = new NpgsqlParameter("Id", NpgsqlDbType.Integer);
            parameterId.Value = 4;
            cmd.Parameters.Add(parameterId);

            NpgsqlParameter parameterDtBegin = new NpgsqlParameter("DtBegin", NpgsqlDbType.Timestamp);
            parameterDtBegin.Value = new DateTime(2017, 9, 1, 0,0,0);
            cmd.Parameters.Add(parameterDtBegin);

            NpgsqlParameter parameterDtEnd = new NpgsqlParameter("DtEnd", NpgsqlDbType.Timestamp);
            parameterDtEnd.Value = new DateTime(2017, 10, 1, 0, 0, 0);
            cmd.Parameters.Add(parameterDtEnd);

            conn.Open();

            using (var reader = cmd.ExecuteReader())  
                  while (reader.Read())
                    Console.WriteLine(reader.GetString(0));
        }
        Console.ReadKey();
    }

I get the following error: {42883: operator does not exist: @ timestamp without time zone} System.Exception {Npgsql.PostgresException}. I saw article (link), but this idea did'n work for me. Thank you for any answer!

score 1 · Accepted Answer · answered Oct 06 '17 at 10:55

it helped me:

NpgsqlCommand cmd = new NpgsqlCommand(@"select Val, Dt from Data where idRef = :id and Dt >= :date and Dt <= :date2 order by Dt asc;", conn);

cmd.Parameters.Add(":id", NpgsqlDbType.Integer).Value = 3;
cmd.Parameters.Add(":date", NpgsqlDbType.Timestamp).Value = new DateTime(2017, 9, 1, 0, 0, 0);
cmd.Parameters.Add(":date2", NpgsqlDbType.Timestamp).Value = new DateTime(2017, 10, 7, 0, 0, 0);

conn.Open();

using (var reader = cmd.ExecuteReader())
    while (reader.Read())
        Console.WriteLine(reader.GetString(0));

PostgreSQL: 42883 Operator does not exist: timestamp without time zone

1 Answers1