End of Month

Get Help
1

Is there a function like eomonth() in sql for metabase? I want to calculate sum of users as at the end of every month using the sql query interface.

2

Hi @Gloria
You can use any function in SQL, which is accepted by your database type.
Metabase just sends the query to your database, which then runs it and returns results to Metabase.

3

thank you @flamber, does this mean the eomonth() is not supported by database?

4

so, metabase is just a mid ware then

5

@Gloria Try looking in your database documentation. Yes, Metabase is just a client.

6

back again, @flamber what function do you advice I use? Or if there is another way in getting end of month. On average I think I think some of postgressql function works for this particular DB

7

@Gloria Since I have absolutely no idea which database you are querying, then I cannot help. Try searching the internet or provide more information.

8

thank you @flamber, I have found a way around it

9

@Gloria Then be a good internet citizen and write which database type you are querying, and the solution/link, so others can learn.

1 Like
10

Relational DB: using postgressql as the language to calculate end of month function
select (date_trunc('month', your_date_column) + interval '1 month - 1 day')::date as end_of_month;
::date in postgressql means you are casting the expression to date, you can also use cast(your_expression as date)new_column_name

or
(date_trunc('MONTH', (your_date_column||'01')::date) + INTERVAL '1 MONTH - 1 day')::DATE

Truncating any date or timestamp to the month level will give you the first of the month containing that date. Adding a month gives you the first of the following month. Then, removing a day will give you the date of the last day of the month of the provided date.

for more reference: https://stackoverflow.com/questions/28186014/how-to-get-the-last-day-of-month-in-postgres

2 Likes