CODEWITHSUNDEEP
mysql
Ben Gordon
Ben Gordon

Reputation: 437

Storing Days in mysql Database

I would like to know the most efficient way of storing days of the week in a mysql database. I have a list of events that occur every week and want to return them in a list format as a schedule on the matching day.

I'm not sure which is the best way to go about this as it's not a specific date just rather a day?

Upvotes: 4

Views: 8232

Answers (4)

Anthony Accioly
Anthony Accioly

Reputation: 22471

For your particular scenario I would go with ENUM type, it is both readable and efficient. 

CREATE TABLE MY_TABLE
(
 -- other fields
  EVENT_DAY ENUM('SUNDAY', 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY')
);

The nice thing about ENUMs is that you can write queries that are easy to read (e.g., WHERE EVENT_DAY = 'FRIDAY') without giving up on performance, since internally MySQL stores data efficiently using a numeric representation.

Just to play the Devil's Advocate, Chris Komlenic wrote a very interesting post recommending Reference Tables instead of enums. Anyway, I think that your problem fits his article "Criteria for when it might be okay to use enum". And a reference table to model days of the week sounds a little bit overkill (I would go with TINYINT as a second option).

Upvotes: 10

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95582

You can store the weekdays and their standard ODBC index numbers in a table. (Use the INNODB engine throughout.)

create table weekdays (
  weekday_num integer primary key,
  day_of_week char(3) not null,
  unique (day_of_week)
);

-- Values correspond to return values from the dayofweek() function.
insert into weekdays values (1, 'Sun');
insert into weekdays values (2, 'Mon');
insert into weekdays values (3, 'Tue');
insert into weekdays values (4, 'Wed');
insert into weekdays values (5, 'Thu');
insert into weekdays values (6, 'Fri');
insert into weekdays values (7, 'Sat');

If application programs need to present these days and their indexes in a list, they can just query the database for the right values. (Or generate code by querying the database during make <your_program_name>.)

Then create a table of recurring events. 

create table recurring_events (
  event_name varchar(20) primary key,
  day_of_week char(3) not null,
  foreign key (day_of_week) references weekdays (day_of_week)
);

insert into recurring_events values
('Trash pickup', 'Mon'),
('Bookmobile', 'Mon'),
('Grocery store', 'Wed'),
('Tutoring', 'Fri');

Using a join means the dbms should be able to avoid evaluating dayofweek() for every row in your table. 

select e.*
from recurring_events e
inner join weekdays w on e.day_of_week = w.day_of_week
and dayofweek(current_date) = w.weekday_num;

Upvotes: 3

favoretti
favoretti

Reputation: 30167

Another option can be to use the ENUM field to store days of week in either integer or string form. Depends on your particular implementation.

Upvotes: 1

JYelton
JYelton

Reputation: 36512

Store it as a normal datetime, and use the function DAYOFWEEK() to determine which day of the week it is.

For example, let's say you want to run a query that will return all rows that are supposed to occur on Monday:

SELECT event
FROM scheduled_events
WHERE DAYOFWEEK(event_datetime) = 2;

Upvotes: 0

Related Questions