How to determine number of days in a month in Presto?

Question

I have data with date, userid, and amount. I want to calculate sum(amount) divided by total day for each month. the final will be presented in monthly basis.

The table I have is looks like this

date       userid  amount
2019-01-01  111  10
2019-01-15  112  20
2019-01-20  113  10
2019-02-01  114  30
2019-02-15  111  20
2019-03-01  115  40
2019-03-23  155  50

desired result is like this

date    avg_qty_sol
Jan-19  1.29
Feb-19  1.79
Mar-19  2.90

avg_qty_sold is coming from sum(amount) / total day for respective month e.g for jan 2019 sum amount is 40 and total days in jan is 31. so the avg_qty_sold is 40/31

Currently Im using case when for this solution. is there any better approach to this?

I guess this post answer to your question : https://stackoverflow.com/questions/691022/how-to-determine-the-number-of-days-in-a-month-in-sql-server — Nkplizz, Sep 10 '19 at 09:54

Piotr Findeisen · Accepted Answer · 2021-03-06T20:59:57.937

Since Presto 318, you this is as easy as:

SELECT day(last_day_of_month(some_date))

See https://trino.io/docs/current/functions/datetime.html#last_day_of_month

Before Presto 318, You can combine date_trunc with EXTRACT:

date_trunc('month', date_value)) gives beginning of the month, while date_add('month', 1, date_trunc('month', date_value)) gives beginning of the next month
subtracting date values returns an interval day to second
EXTRACT(DAY FROM interval) returns day-portion of the interval. You can also use day convenience function instead of EXTRACT(DAY FROM ...). The EXTRACT syntax is more verbose and more standard.

presto:default> SELECT
             ->     date_value,
             ->     EXTRACT(DAY FROM (
             ->         date_add('month', 1, date_trunc('month', date_value)) - date_trunc('month', date_value)))
             -> FROM (VALUES DATE '2019-01-15', DATE '2019-02-01') t(date_value);
 date_value | _col1
------------+-------
 2019-01-15 |    31
 2019-02-01 |    28
(2 rows)

Less natural, but a bit shorter alternative would be to get day number for the last day of given month with day(date_add('day', -1, date_add('month', 1, date_trunc('month', date_value)))):

presto:default> SELECT
             ->     date_value,
             ->     day(date_add('day', -1, date_add('month', 1, date_trunc('month', date_value))))
             -> FROM (VALUES DATE '2019-01-15', DATE '2019-02-01') t(date_value);
 date_value | _col1
------------+-------
 2019-01-15 |    31
 2019-02-01 |    28
(2 rows)

@muhnandap i found a shorter alternative and updated the answer. — Piotr Findeisen, Sep 12 '19 at 21:14

How to determine number of days in a month in Presto?

1 Answers1