SQL to get next monday in Presto

Question

I need to get the date for next Monday irrespective of which day the sql is executed.

I think the logic 7 - day_of_week(current_date)%7 + 1 would work, but then how to get the date.

select current_date;

    _col0
1   2019-11-16


select (7 - day_of_week(current_date)%7+1)


    _col0
1   2

Or is there any other better way to do the same.

I will appreciate any help!!

Martin Traverso · Answer 1 · 2022-04-06T17:52:30.447

5

You can use date_trunc to get the Monday of the current week and add 7 days to it:

presto> select date_trunc('week', current_date) + interval '7' day;

   _col0
------------
 2019-11-18
(1 row)

edited Apr 06 '22 at 17:52

answered Nov 17 '19 at 00:11

Martin Traverso

GMB · Accepted Answer · 2019-11-16T01:24:29.780

2

You could do:

date_add(day, 8 - extract(day_of_week from current_date), current_date)

edited Nov 16 '19 at 01:24

answered Nov 16 '19 at 00:54

GMB

Thank you. Appreciate your help. Just a small change - ```select date_add('day', 8 - extract(day_of_week from current_date), current_date) ``` – Anand Nov 16 '19 at 01:22
@Anand: yes checking the docs I can see that I inverted the value and the timestamp. Fixed. – GMB Nov 16 '19 at 01:25

2 Answers2