Getting results between two dates(inclusive) in PostgreSQL


I'm trying to retreive data between two date, for example, from 2022-11-01 to 2022-11-03.
Here is a fidle : SQL Fiddle
As you can see on the fidle it works just fine, I get all data from the range.
In Metabase using the same query (the date are custom fields)


In Metabase it look like :

WHERE ("STARTDATE" >= {{start}}::date AND "STARTDATE" <= {{end}}::date)

Metabase return data from 2022-11-01 to 2022-11-02, why it exclude 2022-11-03 ?

Hi, for sure it's using 00:00:00 as the time, I would suggest you use a field filter for that query SQL parameters

Hi @Luiggi ,

I tried using a field filter, my query is always in error after that.

FROM {{#2}} as "client"
AND {{period}}

I get the following error :

ERROR: missing entry in FROM clause for table "tbl_client" Position: 18949

@Exe I'm sure I've linked to this a few times before

You mean, I can't use model ?

@Exe You cannot use table aliases with Field Filters. And you cannot control the name from a Sub-Query variable. In other words, you cannot use Field Filters with the example you have provided.

Probably that is because the time that is included. When filtering on end date 'novembre 3 2022' the system reads it as 'novembre 3 2022 0 hrs 0 min'. So the remaining of the day is not included.
What I do in these cases:
"startdate" >= {{Startdate}} and "enddate" <= (cast({{Enddate}} as date) +1 )