DEV Community

@kon_yu
@kon_yu

Posted on

2 2

Make the default of a variable of type Metabase date relative

By writing {{{variable name}}} in the query of metabase, you can input an arbitrary value and display data using a variable.
This feature can be used to set default values for variables.

However, by default, only a fixed date can be set for a variable's date type, and it is not possible to specify a range such as Last week.
We show how to solve this problem by devising SQL.

Prerequisites.

Metabase uses PostgreSQL as the data source DBMS

SQL representing the default period

Use coalesce and current_date, and use current_date if no variable is set.

SQL extracted from the target

s.created_at 
  BETWEEN 
    coalesce([[{{from_date}},]]] current_date - '1 week'::interval)
  and
    coalesce([[{{to_date}},]]] current_date) 
Enter fullscreen mode Exit fullscreen mode

SQL examples for each date using the above

select 
    sum(user_id) ,
    date_trunc('day', created_at) as day
from
  a_table
where 
    created_at BETWEEN coalesce([[{{from_date}},]]] current_date - '1 week'::interval) and coalesce([[{{to_date}},]]] current_date) 
group by date_trunc('day', created_at)
order by date_trunc('day', created_at)
Enter fullscreen mode Exit fullscreen mode

ref: https://www.wantedly.com/projects/432393?fbclid=IwAR1_jZ0-xxi2jSl3CIlA-j9PDLZOz-msEGEWc5ekeMxPQB38fIbw5fsyBD4

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read full post →

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more