CODEWITHSUNDEEP
postgresqlpostgresql-9.4dayofweek
ere
ere

Reputation: 1779

Finding the next occurrence of particular day of the week

How can I take a datetime column start_at convert it to a day of week and find out the next future occurrence relative to the current date?

Here I'm trying to add the DOW to the current week but it doesn't appear to be correct.

SELECT date_trunc('week', current_date) + CAST(extract(dow from start_at) || ' days' AS interval)

Full example:

SELECT id event_id,
GENERATE_SERIES(date_trunc('week', current_date) + CAST(extract(dow from start_at) + 1 || ' days' AS interval) + start_at::time, current_date + interval '3 weeks', '1 week'::INTERVAL) AS start_at
FROM events

Events

+-----+---------------------------+---------------------+
| id  | start_at                  | recurring_schedule  |
+-----+---------------------------+---------------------+
| 358 | 2015-01-23 20:00:00 +0000 | Weekly              |
| 359 | 2016-01-22 19:30:00 +1100 | Monthly             |
| 360 | 2016-02-01 19:00:00 +1100 | Weekly              |
| 361 | 2016-02-01 20:00:00 +0000 | Weekly              |
| 362 | 2014-02-13 20:00:00 +0000 | Bi-Weekly           |
+-----+---------------------------+---------------------+

Output

+----------+---------------------------+
| event_id | start_at                  |
+----------+---------------------------+
| 35       | 2018-04-11 19:30:00 +0000 |
| 94       | 2018-04-12 20:00:00 +0100 |
| 269      | 2018-04-13 18:30:00 +0100 |
| 45       | 2018-04-13 20:00:00 +0100 |
| 242      | 2018-04-13 19:30:00 +1100 |    
| 35       | 2018-04-18 19:30:00 +0000 |
| 94       | 2018-04-19 20:00:00 +0100 |
| 269      | 2018-04-20 18:30:00 +0100 |
| 45       | 2018-04-20 20:00:00 +0100 |
| 242      | 2018-04-20 19:30:00 +1100 |
+----------+---------------------------+

Upvotes: 1

Views: 89

Answers (1)

holden
holden

Reputation: 13571

Give this a try:

SELECT id event_id,    
GENERATE_SERIES(date_trunc('week', current_date)::date 
+ (extract(isodow from start_at)::int - 1) + start_at::time, current_date 
+ interval '3 weeks', '1 week'::INTERVAL) AS start_at
FROM events

Upvotes: 1

Related Questions