Display Next Event Date

Display next event date

Your first step is to get your event start dates with each event, and the repeat interval, to do this you can use:

SELECT  EventID = e.ID, 
        e.Name, 
        StartDateTime = DATEADD(SECOND, rs.Meta_Value, '19700101'),
        RepeatInterval = ri.Meta_Value
FROM    dbo.Events e
        INNER JOIN dbo.Events_Meta rs
            ON rs.Event_ID = e.ID
            AND rs.Meta_Key = 'repeat_start'
        INNER JOIN dbo.Events_Meta ri
            ON ri.Event_ID = e.ID
            AND ri.Meta_Key = 'repeat_interval_' + CAST(e.ID AS VARCHAR(10));

This gives:

EventID | Name         | StartDateTime       | RepeatInterval
--------+--------------+---------------------+-----------------
   1    | Billa Vist   | 2014-01-03 10:00:00 |     604800
   1    | Billa Vist   | 2014-01-04 18:00:00 |     604800

To get this to repeat you will need a numbers table to cross join to, if you don't have one there are a number of ways to generate one on the fly, for simplicity reasons I will use:

WITH Numbers AS
(   SELECT  Number = ROW_NUMBER() OVER(ORDER BY a.object_id) - 1
    FROM    sys.all_objects a
)
SELECT  Number
FROM    Numbers;

For further reading, Aaron Bertrand has done some in depth comparisons ways of generating sequential lists of numbers:

• Generate a set or sequence without loops – part
  1
• Generate a set or sequence without loops – part
  2
• Generate a set or sequence without loops – part
  3

If we limit our numbers table to only 0 - 5, and only look at the first event, cross joining the two will give:

EventID | Name         | StartDateTime       | RepeatInterval | Number
--------+--------------+---------------------+----------------+---------
   1    | Billa Vist   | 2014-01-03 10:00:00 |     604800     |    0
   1    | Billa Vist   | 2014-01-03 10:00:00 |     604800     |    1
   1    | Billa Vist   | 2014-01-03 10:00:00 |     604800     |    2
   1    | Billa Vist   | 2014-01-03 10:00:00 |     604800     |    3
   1    | Billa Vist   | 2014-01-03 10:00:00 |     604800     |    4
   1    | Billa Vist   | 2014-01-03 10:00:00 |     604800     |    5

Then you can get your occurance by adding RepeatInterval * Number to the event start time:

DECLARE @EndDate DATETIME = '20140130';

WITH EventData AS
(   SELECT  EventID = e.ID, 
            e.Name, 
            StartDateTime = DATEADD(SECOND, rs.Meta_Value, '19700101'),
            RepeatInterval = ri.Meta_Value
    FROM    dbo.Events e
            INNER JOIN dbo.Events_Meta rs
                ON rs.Event_ID = e.ID
                AND rs.Meta_Key = 'repeat_start'
            INNER JOIN dbo.Events_Meta ri
                ON ri.Event_ID = e.ID
                AND ri.Meta_Key = 'repeat_interval_' + CAST(rs.ID AS VARCHAR(10))
), Numbers AS
(   SELECT  Number = ROW_NUMBER() OVER(ORDER BY a.object_id) - 1
    FROM    sys.all_objects a
)
SELECT  e.EventID,
        e.Name,
        EventDate = DATEADD(SECOND, n.Number * e.RepeatInterval, e.StartDateTime)
FROM    EventData e
        CROSS JOIN Numbers n
WHERE   DATEADD(SECOND, n.Number * e.RepeatInterval, e.StartDateTime) < @EndDate
ORDER BY e.EventID, EventDate;

This gives your expected output:

EVENTID | NAME          | EVENTDATE
--------+---------------+--------------------------------
   1    | Billa Vist    | January, 03 2014 10:00:00+0000
   1    | Billa Vist    | January, 04 2014 18:00:00+0000
   1    | Billa Vist    | January, 10 2014 10:00:00+0000
   1    | Billa Vist    | January, 11 2014 18:00:00+0000
   1    | Billa Vist    | January, 17 2014 10:00:00+0000
   1    | Billa Vist    | January, 18 2014 18:00:00+0000
   1    | Billa Vist    | January, 24 2014 10:00:00+0000
   1    | Billa Vist    | January, 25 2014 18:00:00+0000

Example on SQL Fiddle

---

I think the schema you have is questionable though, the join on:

Meta_Key = 'repeat_interval_' + CAST(rs.ID AS VARCHAR(10))

is flimsy at best. I think you would be much better off storing the start date and repeat interval associated with it together:

CREATE TABLE dbo.Events_Meta
(       ID INT IDENTITY(1, 1) NOT NULL,
        Event_ID INT NOT NULL,
        StartDateTime DATETIME2 NOT NULL,
        IntervalRepeat INT NULL, -- NULLABLE FOR SINGLE EVENTS
        RepeatEndDate DATETIME2 NULL, -- NULLABLE FOR EVENTS THAT NEVER END
    CONSTRAINT PK_Events_Meta__ID PRIMARY KEY (ID),
    CONSTRAINT FK_Events_Meta__Event_ID FOREIGN KEY (Event_ID) REFERENCES dbo.Events (ID)
);

This would simplify your data to:

EventID | StartDateTime       | RepeatInterval | RepeatEndDate
--------+---------------------+----------------+---------------
   1    | 2014-01-03 10:00:00 |    604800      |     NULL
   1    | 2014-01-04 18:00:00 |    604800      |     NULL

It also allows you to add an end date to your repeat, i.e. if you only want it to repeat for one week. This then your query simlifies to:

DECLARE @EndDate DATETIME = '20140130';
WITH Numbers AS
(   SELECT  Number = ROW_NUMBER() OVER(ORDER BY a.object_id) - 1
    FROM    sys.all_objects a
)
SELECT  e.ID,
        e.Name,
        EventDate = DATEADD(SECOND, n.Number * em.IntervalRepeat, em.StartDateTime) 
FROM    Events e
        INNER JOIN Events_Meta em
            ON em.Event_ID = e.ID
        CROSS JOIN Numbers n
WHERE   DATEADD(SECOND, n.Number * em.IntervalRepeat, em.StartDateTime) <= @EndDate
AND (   DATEADD(SECOND, n.Number * em.IntervalRepeat, em.StartDateTime) <= em.RepeatEndDate 
    OR  em.RepeatEndDate IS NULL
    )
ORDER BY EventDate;

Example on SQL Fiddle

---

I won't give you my full schema for how I have achieved this in the past, but I will give a very cut down example, from which you can hopefully build your own. I will only add an example for an event that occurs weekly on Mon-Fri:

In the above ER RepeatEvent stores the basic information for the recurring event, then depending on the repeat type (Daily, weekly, monthly) one or more of the other tables is populated. In example of a weekly event, it would store all the days of the week that it repeats in in the table RepeatDay. If this needed to be limited to only certain months, you could store these months in RepeatMonth, and so on.

Then using a calendar table you can get all the possible dates after the first date, and limit these to only those dates that match the day of the week/month of the year etc:

WITH RepeatingEvents AS
(   SELECT  e.Name,
            re.StartDateTime,
            re.EndDateTime,
            re.TimesToRepeat,
            RepeatEventDate = CAST(c.DateKey AS DATETIME) + CAST(re.StartTime AS DATETIME),
            RepeatNumber = ROW_NUMBER() OVER(PARTITION BY re.RepeatEventID ORDER BY c.Datekey)
    FROM    dbo.Event e
            INNER JOIN dbo.RepeatEvent re
                ON e.EventID = re.EventID
            INNER JOIN dbo.RepeatType rt
                ON rt.RepeatTypeID = re.RepeatTypeID
            INNER JOIN dbo.Calendar c
                ON c.DateKey >= re.StartDate
            INNER JOIN dbo.RepeatDayOfWeek rdw
                ON rdw.RepeatEventID = re.RepeatEventID
                AND rdw.DayNumberOfWeek = c.DayNumberOfWeek
    WHERE   rt.Name = 'Weekly'
)
SELECT  Name, StartDateTime, RepeatEventDate, RepeatNumber
FROM    RepeatingEvents
WHERE   (TimesToRepeat IS NULL OR RepeatNumber <= TimesToRepeat)
AND     (EndDateTime IS NULL OR RepeatEventDate <= EndDateTime);

Example on SQL Fiddle

This is only a very basic representation of how I implemented it, for instance I actually used entirely views any query for the repeating data so that any event with no entries in RepeatDayOfWeek would be assumed to repeat every day, rather than never. Along with all the other detail in this and other answers, you should hopefully have more than enough to get you started.

How Do I Get The Events In Between Start and End Time To Display On My Calendar

I would calculate the duration (number of days), and then use that duration as the number of times to loop. Then to get the date for each day of the duration, I would use a counter variable (enumerator maybe) and add it to the date. For example when the counter is 2, then I would add 2 days to the start date to get the current date in the loop.

Hopefully this would give you an idea.

Using your example, add this to your view:

from datetime import timedelta, date

# Add this function outside the View, just like you did with the other ones
def daterange(date1, date2):
    for n in range(int ((date2 - date1).days)+1):
        yield date1 + timedelta(n)

def get_context_data(self, **kwargs):
    ## other code here ##
    vacation_calendar_requests = VacationRequest.objects.filter(start_time__year = d.year,start_time__month = d.month)
    
    # this will add a new field, "dates", to each event
    for request in vacation_calendar_requests:
        start_date = request.start_time
        end_date = request.end_time
        start_dt = date(start_date.year,start_date.month,start_date.day) # not sure if this is how you get your event start date
        end_dt = date(end_date.year,end_date.month,end_date.day) # not sure if this is how you get your event end date
        request.dates = [date for date in daterange(start_dt, end_dt)] 
    
    ## rest of code here ##

Then in your template, add this nested loop to show the dates for each event:

{% for date in request.dates %}
   {{ request.vacation_request_name }}
   {{ date }} /* format the date accordingly */
{% endfor %}

Only display upcoming events in Wordpress Widget

you can WP_Query(). check the code below.

$args = array(
    'post_type'      => 'events',
    'posts_per_page' => -1,
    'meta_key'       => 'f21_event_startdate',
    'meta_value'     => date('Ymd'),
    'meta_compare'   => '>=',
    'orderby'        => 'rand',
    'order'          => 'ASC'
);

$upcoming_events = new WP_Query( $args );

jQuery.eCalendar() - only display events onclick on date

You have to modify the library by adding a click event.

Here's what I added to the library.
First, I created a click event :

    //Add click event for each day with events on calender
    var mouseClickEvent = function () {
        $(".c-event-item").hide();
        var d = $(this).attr('data-event-day');
                $('div.c-event[data-event-day="' + d + '"]').show();
        $('div.c-event-item[data-event-day="' + d + '"]').show();
    };

on the print(), register the mouseClickEvent for each days.

if (d.getDate() == day && (d.getMonth() - 1) == dMonth && d.getFullYear() == dYear) {
    cDay.addClass('c-event').attr('data-event-day', d.getDate());
    cDay.on('mouseover', mouseOverEvent).on('mouseleave', mouseLeaveEvent);
    //register the click events
    cDay.on('mousedown', mouseClickEvent)
}

Also, don't forget that you need to hide the event list by default by adding this code on print()

//hide the eventlist by default
item.attr('style', 'display:none');

You can check my modification here :
https://jsfiddle.net/cuaowret/

Custom Post Type: Event - Display Upcoming Events for Next 30 Days

If dates are stored as a formatted text string, you'll need to convert them back to a date, then filter and sort them. My guess is that when you run this code, an event on 03 June will show before a date on 30 January.

Here's how I would approach this:

<?php 
//Get all the events
$eventsOpt = array(         
    'post_type' => 'ptype_events',
    'posts_per_page' => -1,
    'meta_key' => 'ptype_event_date',
    'orderby' => 'meta_value',
    'order' => 'ASC',
);
$events_query = new WP_Query($eventsOpt);

//Loop through them and filter out events before today and events in more than 1 month
$events_out = array();
while ($events_query->have_posts()) : $events_query->the_post(); 
   $ed = strtotime( get_post_meta($post->ID, 'ptype_event_date', true) );
   $temp = array();
   if ( $ed !== FALSE && $ed >= time() && $ed <= strtotime('+1 month') ) :
      $temp['date'] = date('d F, Y', $ed);
      $temp['title'] = get_the_title();
      $events_out[$ed] = $temp;
   endif;
endwhile;
wp_reset_query();

//Sort the events by date
ksort( $events_out );

//Write the output
$format = <<<EOF
<div class="eventsPost clearfix">
   <h3 class="title">
      %s
   </h3>
   Date: %s </div>
EOF;

foreach ($events_out as $event) {
   printf ($format, $event['title'], $event['date']);
} ?>

---

Related Topics