SQL abstract out date and find the next one this week

Question

I have a set of records which store weekly events by using a datetime... start_at from the first week they began.

It's not the REAL DATE, it's storing the DAY (DOW) and TIME, but is not the date I'm actually looking for.

I'm trying to figure out how I can convert/abstract the start_at date in a way so using that I can find all of the next events that fall within the next 24 hours.

START_AT -> 2010-09-12 16:00:00 -> CONVERT into 2013-09-08 16:00:00

but I'm not sure how to abstract the start_at for the upcoming day of the week.

I figured out how to order them but I'm not sure how to do what I want:

SELECT * FROM events ORDER BY (EXTRACT(dow FROM start_at)::int + 7 - EXTRACT(dow FROM now())::int) % 7,start_at::time

Any help would be greatly appreciated!

score 2 · Answer 1 · answered Sep 12 '13 at 16:33

2

If you mean the next 24 hours:

SELECT * 
FROM   events
WHERE  start_at BETWEEN now() AND now() + INTERVAL '1 day'

If you mean the next 24 hour period starting at midnight (i.e. all day tomorrow):

SELECT *
FROM   events
WHERE  start_at BETWEEN now()::date + INTERVAL '1 day' AND
                        now()::date + INTERVAL '2 days'

answered Sep 12 '13 at 16:33

paul

21,653
1
53
54

Well that's part of it, but the part I can't figure out the hard part is that the start_at date isn't the date i need. It's just a storage for the weekday and time. It might be 12/12/2001 at 7:00... I need to be able to abstract the date into the same day and time for this week then find the BETWEEN part. Sorry, I wasn't more clear. – ere Sep 12 '13 at 16:43

score 1 · Answer 2 · edited May 23 '17 at 12:05

If I've understood you correctly, you store the date a recurring event starts on, and you need to know which recurring events happen in the next 24 hours.

This is probably easiest to handle with a join on a calendar table. The CTE "events" below takes the place of whatever your table is called. It stores events we expect to recur weekly, starting on Jan 3, Jan 4, and Jan 5. As I write this, it's Thursday.

with events as (
  select date '2013-01-03' start_at
  union all 
  select date '2013-01-04'
  union all 
  select date '2013-01-05'
), event_days as (
  select events.start_at, c.day_of_week
  from events
  inner join calendar c on c.cal_date = events.start_at
)
select *
from event_days
inner join calendar on event_days.day_of_week = calendar.day_of_week
where cal_date between current_date and current_date + interval '1 days';

start_at    day_of_week  cal_date    year_of_date  month_of_year  day_of_month  day_of_week
2013-01-03  Thu          2013-09-12  2013          9              12            Thu
2013-01-04  Fri          2013-09-13  2013          9              13            Fri

Code for a calendar table in PostgreSQL.

"The next 24 hours" is a little fuzzy if you're storing just the date.

score 1 · Accepted Answer · answered Sep 12 '13 at 17:46

If I understand you correctly you may be over thinking it. If all you're looking for is a recurring datetime 24 hour before you could do something like:

SELECT * FROM events EXTRACT(dow FROM start_at) = 1

Where 1 is the DOW (for Monday)... you abstract the DOW from the date

The day of the week as Sunday(0) to Saturday(6)

and unless you very fussed about the Time, you could just search for the DOW before the day you want (~24 hours).

SQL abstract out date and find the next one this week

3 Answers3