CODEWITHSUNDEEP
javascriptpostgresqlcalendarvuetify.js
TheLoop
TheLoop

Reputation: 1

V-Calendar component in Vuetify; setting up events to scale across months

I'm looking for advice on the best way to store event dates in Postgres when it comes to fetching them and displaying them on an calendar. I'm using an node/expressjs backend with postgres as a data store. On the front end I'm using vue with vuetify/nuxt. Vuetify has a lot of convenient UI components, but more specifically the v-calendar component:

V-Calendar

I've got a few edge cases that I'm having a hard time wrapping my head around.

I want to be able to fetch events for the current month from the database and events that spill over from one month to the next, and to the next, etc. What is the best way to do this? How should I model my database table and fetch the records (I'm using Postgres)? An event needs a name, start and end. Should I instead store the total duration of the event in a unix timestamp and query the events by range between a given month duration (in seconds)?

Any advice would be welcome.

Upvotes: 0

Views: 2188

Answers (2)

dustbuster
dustbuster

Reputation: 82152

The way we stored and retrieved events was that every time a user scrolls in the calendar i use a method to return start_date_time for the current month and the previous and next month. For a total of 3 months. This way we catch any calendar overlap. We use laravel in the backend, but you should be able to get the general gist of the method. Our tableify method just formats data for us. My DB structure is as follows (removing subjective data):

CREATE TABLE calendar_events (
    id bigserial NOT NULL,
    calendar_event_category_id int4 NOT NULL, 
    name varchar(512) NOT NULL,
    description text NULL,
    start_date_time timestamp(0) NOT NULL,
    end_date_time timestamp(0) NULL,
    "data" json NULL,
    user_id int4 NULL,
    created_at timestamp(0) NULL,
    updated_at timestamp(0) NULL,
    CONSTRAINT calendar_events_pkey PRIMARY KEY (id),
    CONSTRAINT calendar_events_calendar_event_category_id_foreign FOREIGN KEY (calendar_event_category_id) REFERENCES calendar_event_categories(id),
    CONSTRAINT calendar_events_user_id_foreign FOREIGN KEY (user_id) REFERENCES users(id)
);

My index method:


public function index(Request $request)
{
    $currentDate = empty($request->filter_date) ? Carbon::now() : new Carbon($request->filter_date);
    if (! empty($request->filter_date)) {
        return api_response('Retrieved Calendar Events.',
            CalendarEvent::tableify($request,
                CalendarEvent::where('start_date_time', '>=', $currentDate->subMonth(1)->isoFormat('YYYY-MM-DD'))->where('start_date_time', '<=', ($currentDate->addMonth(2)->isoFormat('YYYY-MM-DD')))->orderby('start_date_time', 'DESC')
            )
        );
    } else {
        return api_response('Retrieved Calendar Events.', CalendarEvent::tableify($request, CalendarEvent::orderby('start_date_time', 'DESC')));
    }
}

That's the way I solved the overlap problem. Every time the user scrolls the frontend checks if a month was changed, if so, it updates the calendar with the latest 3 month chunk.

Upvotes: 0

Mike Organek
Mike Organek

Reputation: 12484

Store your events with their beginning and end dates in a range type

You can then use the overlap && range operator to figure out which events belong on a certain month's calendar.

For instance, if you have an event with duration column of type daterange defined as '[2020-01-01, 2020-03-31]'::daterange, it will be match the following condition:

  where duration && '[2020-02-01, 2020-03-01)'

Please note that the closing ) is deliberate since that excludes the upper limit from the range (in this case, 1 March).

In case you would rather not store the start and end dates inside a range type, you can always construct one on the fly:

  where daterange(start_date, end_date, '[]') && '[2020-02-01, 2020-03-01)'

The range for the current month can be calculated on the fly:

select daterange(
         date_trunc('month', now())::date, 
        (date_trunc('month', now()) + interval '1 month')::date, '[)'
       );

        daterange        
-------------------------
 [2020-07-01,2020-08-01)
(1 row)

Or for a three-month calendar:

select daterange(
         (date_trunc('month', now()) - interval '1 month')::date, 
         (date_trunc('month', now()) + interval '2 month')::date, '[)'
       );

        daterange        
-------------------------
 [2020-06-01,2020-09-01)
(1 row)

Upvotes: 0

Related Questions