How to query Postgresql-14 Timestamptz field in C#?

Question

I use Postgresql-14, I have a timestamptz field named 'start_datetime' like below:

select start_datetime from table1

2023-08-01 07:00:00.000 +0700
2023-08-01 07:00:00.000 +0700
2023-08-02 07:00:00.000 +0700
2023-08-03 07:00:00.000 +0700
2023-08-04 07:00:00.000 +0700
2023-08-04 07:00:00.000 +0700

How to query date that timestamptz field with C# Code, I expect to query date '2021-08-02' and '2021-08-03'

I use:

• Net Core 6
• Dapper 2.0.143
• Npgsql 7.0.4
• Npgsql.EntityFrameworkCore.PostgreSQL 7.0.4

I have tried:

select
   t.start_datetime
    from
        table1 t
    where
       to_char(t.start_datetime at time zone 'UTC','YYYY-MM-DD') >=
       '2023-08-02'
        and to_char(c.start_datetime at time zone 'UTC','YYYY-MM-DD') <= '2023-08-03'
        

but it didn't find it

Answer 1

Instead of using to_char, consider using date_trunc:

SELECT DATE_TRUNC('day', start_datetime AT TIME ZONE 'UTC') AS utc_date
FROM table1
WHERE DATE_TRUNC('day', start_datetime AT TIME ZONE 'UTC') BETWEEN '2021-08-02' AND '2021-08-03'

You note in your question that you're querying the year 2023 but the table contains only 2021, so that may also be part of the problem you're encountering, or a mistake in framing the question.