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

k3davis · Accepted Answer · 2023-08-08 01:38:31Z

0

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.

answered Aug 8, 2023 at 1:38

k3davis

1,10515 silver badges29 bronze badges

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

2 Comments

Donald Over a year ago

yes, thank you for your info, I have updated the query results. why I have to move back 1 day to start in my case '2023-08-01' to get the '2023-08-02', is it because the UTC?

k3davis Over a year ago

Yes, I believe so :)

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related