Npgsql COUNT query returns 0 even though data exists with matching date and EmployeeId in PostgreSQL

Question

I have a code that checks if a record exists:

  using (var connection = new NpgsqlConnection(_connectionString))
            {
                await connection.OpenAsync();
                
                var query = @"
                SELECT COUNT(1)
               FROM TimeEntries
                  WHERE EmployeeId = @EmployeeId AND Date::date = @Date";
                
                using (var command = new NpgsqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@EmployeeId", timeEntry.EmployeeId);
                    command.Parameters.AddWithValue("@Date", timeEntry.Date.Date);  
                    
                    var count = (long)await command.ExecuteScalarAsync();

                    if (count > 0)
                    {
                        throw new InvalidOperationException("Pracownik już ma zarejestrowane godziny pracy w tej dacie.");
                    }
                }

and i send there request like:

{
  "date": "2024-12-29T21:42:58.251Z",
  "hoursWorked": 0
}

and id in a query.

The problem is that it doesnt work.

The data in a table are as follow:

"id"    "employeeid"    "date"  "hoursworked"
7             2       "2024-12-29"      8.00

I thought it doesnt work because here:

command.Parameters.AddWithValue("@Date", timeEntry.Date.Date);

the value of a parameter is 29.12.2024 00:00:00 instead of 29.12.2024 but the sql query:

SELECT COUNT(1)
FROM TimeEntries
WHERE EmployeeId = 2 AND Date::date = '2024-12-29 00:00:00';

return the proper value of 10, but in code the count is always 0...

It works with:

   var data = new DateTime(timeEntry.Date.Year, timeEntry.Date.Month, 
timeEntry.Date.Day, 0, 0, 0);
                    command.Parameters.AddWithValue("@EmployeeId", timeEntry.EmployeeId);
                    command.Parameters.AddWithValue("@Date", data);

but still i dont understand why previous version didnt work...

Run the code in the debugger. In the debugger, check the values of all of the properties of timeEntry (using the Immediate Window or Watch Window) and include those in the question. Do not guess what the values are. Use the debugger. — mjwills
– mjwills, Commented Dec 29, 2024 at 22:18
I'd suggest being explicit about which date type you want - stackoverflow.com/a/49086150/34092 . — mjwills
– mjwills, Commented Dec 29, 2024 at 22:36

Zegarek · Accepted Answer · 2024-12-30 05:44:30Z

The request you're sending shows a timestamptz-type value. Even if you cast the Date column to a ::date explicitly like you did, comparing it to a timestamptz will not make Postgres pick a date = date operator and autocast timestamptz::date on the right to match the left operand, truncating away anything below days, if that's what you wanted.

My initial guess was that you're seeing Postgres use type preference in pg_type.typispreferred to pick the best = operator candidate, casting the date on the left to a timestamptz that is the preference in the 'D' type group it shares with date and arriving at timestamptz = timestamptz rather than date = date. As it turns out, there's a date = timestamptz already, so that's not necessary:
_{demo at db<>fiddle}

select oid::regoperator
      ,oprname
      ,oprleft::regtype
      ,oprright::regtype
      ,oprresult::regtype
      ,oprcode
from pg_operator
where oprname='='
and oprleft='date'::regtype;

oid	oprname	oprleft	oprright	oprresult	oprcode
=(date,date)	=	date	date	boolean	date_eq
=(date,timestamp without time zone)	=	date	timestamp without time zone	boolean	date_eq_timestamp
=(date,timestamp with time zone)	=	date	timestamp with time zone	boolean	date_eq_timestamptz

10.2. Operators describes the type conversion rules that apply here and this case gets resolved before reaching step 3.d. What you're seeing is the timestamptz you send as @Date gets shifted according to your TimeZone before the comparison:

set timezone='US/Pacific';

select '2024-12-29 00:00:00Z'::date
     = '2024-12-29 00:00:00Z'::timestamptz;


False

You can use explain verbose to see what Postgres did to the value before running the query:

explain verbose
select '2024-12-29 00:00:00Z'::date
     = '2024-12-29 00:00:00Z'::timestamptz

QUERY PLAN
Result (cost=0.00..0.01 rows=1 width=1)
Output: ('2024-12-29'::date = '2024-12-28 16:00:00-08'::timestamp with time zone)

Make sure timeEntry.Date.Date gets sent as a PostgreSQL type date, or adjust your session's TimeZone setting if it's supposed to match those rows. Your 2024-12-29T21:42:58.251Z is close enough to midnight that any zone east of +03:00 will see it as 2024-12-30. Also, your clients in different time zones get different values for Date::date if that column is also a timestamptz:

create table timeentries("date")as values
 ('2024-12-29 21:00:00+00'::timestamptz)
,('2024-12-29 20:59:59.999999+00')
,('2024-12-30 02:59:59.999999+00')
,('2024-12-30 03:00:00+00');

set timezone='UTC';
select date
      ,date::date as "::date" 
from timeentries;

date	::date
2024-12-29 21:00:00+00	2024-12-29
2024-12-29 20:59:59.999999+00	2024-12-29
2024-12-30 02:59:59.999999+00	2024-12-30
2024-12-30 03:00:00+00	2024-12-30

set timezone='UTC+03';
select date
      ,date::date as "::date" 
from timeentries;

date	::date
2024-12-29 18:00:00-03	2024-12-29
2024-12-29 17:59:59.999999-03	2024-12-29
2024-12-29 23:59:59.999999-03	2024-12-29
2024-12-30 00:00:00-03	2024-12-30

set timezone='UTC-03';
select date
      ,date::date as "::date" 
from timeentries;

date	::date
2024-12-30 00:00:00+03	2024-12-30
2024-12-29 23:59:59.999999+03	2024-12-29
2024-12-30 05:59:59.999999+03	2024-12-30
2024-12-30 06:00:00+03	2024-12-30

Collectives™ on Stack Overflow

Npgsql COUNT query returns 0 even though data exists with matching date and EmployeeId in PostgreSQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related