Getting results between two dates(inclusive) in PostgreSQL

Get Help
1

Hi,

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)

image

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 ?

2

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

3

Hi @Luiggi ,

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

SELECT "ID"
FROM {{#2}} as "client"
WHERE "TYPE" = 'LOW'
AND {{period}}

I get the following error :

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

4

@Exe I'm sure I've linked to this a few times before
https://www.metabase.com/learn/sql-questions/field-filters#field-filters-are-incompatible-with-aliasing

5

You mean, I can't use model ?

6

@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.

7

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 )