0

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

Amit Joshi
  • 15,448
  • 21
  • 77
  • 141
Donald
  • 551
  • 2
  • 6
  • 22

1 Answers1

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.

k3davis
  • 985
  • 12
  • 29
  • 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? – Donald Aug 08 '23 at 01:57
  • Yes, I believe so :) – k3davis Aug 08 '23 at 02:07