12

I am using Npgsql 3.0.3.0 and PetaPoco latest version.

When I run this command:

var dateCreated = DateTime.Now; // just an example
var sql = new Sql("WHERE date_created = @0", dateCreated.ToString("yyyy-MM-dd HH:00:00"));
var category = db.SingleOrDefault<Category>(sql);

I get the following error:

Npgsql.NpgsqlException 42883: operator does not exist: timestamp without time zone = text

I understand the error message is saying I'm trying to compare a timestamp (date) with a text, however for me it's perfectly valid to compare them as I am expecting the following SQL statement to be built:

SELECT * FROM category WHERE date_created = '2017-02-03 15:00:00'

I don't really want to typecast my database column to text for performance reasons.

AndreFeijo
  • 10,044
  • 7
  • 34
  • 64

3 Answers3

11

You need to cast value to timestsamp:

var sql = new Sql("WHERE date_created = @0::timestamp", dateCreated.ToString("yyyy-MM-dd HH:00:00"));
Roman Tkachuk
  • 3,096
  • 1
  • 16
  • 15
5

You need to explicit typecast text to timestamp.

Try using :

TO_TIMESTAMP(date_string,'YYYY-MM-DD')
Rajesh Jaiswal
  • 81
  • 1
  • 5
1

As @roman-tkachuk answered, you can tell PostgreSQL to cast your string into a timestamp.

Or better yet, you can simply send a timestamp to PostgreSQL directly, rather than sending a string representation of a timestamp and having it cast. To do that, simply send dateCreated directly, without the ToString().

Shay Rojansky
  • 15,357
  • 2
  • 40
  • 69
  • 1
    Reason why I'm formatting the datetime with `ToString()` is that I need to ignore minutes and seconds. – AndreFeijo Feb 04 '17 at 01:36
  • You can do that just as well by creating a DateTime in C# with the minutes and seconds zeroed out. That's effectively what you're doing, just via a string, which is a less "tight". Definitely not very important - just mentioning it. – Shay Rojansky Feb 04 '17 at 09:07