Enter time period in database [closed]

First, let's answer: Yes and no. Because? Yes, there are ways to solve your problem, in order to store two dates, one referring to the beginning of a temporal event, another referring to its end. No, because it is not possible to do this in just one column in your database (this does not even make sense). That is, to store a range of dates, we have to create two columns in the table, one to store the beginning, the other for the term.

For purposes of justifying the later examples, let's consider a table in the following format:

create table eventos (
  id int not null auto_increment primary key,
  nome varchar(255),
  inicio date,
  fim date
);

A basic table, with unique high id , a name in text format, and two fields of type date , and end , to store the beginning and end of each event, as you might imagine. Now let's populate this table with some basic records:

insert into eventos (nome, inicio, fim) values ('Carnaval',  '2017-02-24', '2017-02-28');
insert into eventos (nome, inicio, fim) values ('Férias',    '2017-01-01', '2017-02-28');
insert into eventos (nome, inicio, fim) values ('Foo',       '2017-02-03', '2017-02-07');
insert into eventos (nome, inicio, fim) values ('Hoje',      '2017-02-01', '2017-02-01');
insert into eventos (nome, inicio, fim) values ('Reveillon', '2016-12-31', '2017-01-01');

Now we want to find out what events are happening on a given day, right? Let's assume we return the events on 04/02/2017. Just execute the following statement:

select id, nome from eventos where inicio <= "2017-02-04" and fim >= "2017-02-04";

  

You can alternatively use where "2017-02-04" between inicio and fim to produce the same result, making the statement more readable.

Basically we are saying to SQL: From the eventos table, return me the values of id and nome of events with start date smaller than (before) 04/02/2017 and end date greater which (after) 04/02/2017.

The result? It is shown below:

+----+---------------------+
| id | nome                |
+----+---------------------+
| 2  | Férias              |
+----+---------------------+
| 3  | Foo                 |
+----+---------------------+

  

The equals signal in the comparisons in where is intended to include the desired day in the desired range .

Have we solved the problem yet? Yes, but what about making it more dynamic?

We can change the SQL statement so that it always looks for the events of the current day, without having to identify the day through an external language. We can do this through the command curdate() , as below:

select id, nome from eventos where inicio <= curdate() and fim >= curdate();

  

You can alternatively use where curdate() between inicio and fim to produce the same result, making the statement more readable.

In this way, the result will be, for today, 01/02/2017, the following:

+----+---------------------+
| id | nome                |
+----+---------------------+
| 2  | Férias              |
+----+---------------------+
| 4  | Hoje                |
+----+---------------------+

Cool, but how about looking for all events that have already ended? No problem.

select id, nome from eventos where fim < curdate();

Resulting in:

+----+---------------------+
| id | nome                |
+----+---------------------+
| 5  | Reveillon           |
+----+---------------------+

Future events? Same idea.

select id, nome from eventos where inicio > curdate();

Resulting in:

+----+---------------------+
| id | nome                |
+----+---------------------+
| 1  | Carnaval            |
+----+---------------------+
| 4  | Foo                 |
+----+---------------------+

  

Remembering that the results presented in the last three instructions, which involve using the curdate() function, vary depending on the day you are running. In this way, the results referring to 01/02/2017 were presented.

You can still check all the instructions here that are working here . The results of each statement are presented in separate tables at the bottom of the page.