Insert time period into database

Question

I was thinking here... Is there any method to enter in the database several dates at the same time?

For example an event that starts on 03/02 and goes until 07/02. How to insert this interval in Mysql so that every day between 03 and 07 a post remains active on a site, so to speak?

Thanks in advance

Hugs

Answer 1

First, let’s answer: Yes and no. Why? Yes, because 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 (it doesn’t even make so much sense). That is, to store a crease of dates, we have to create two columns in the table, one to store the start, the other to finish.

For the purpose of justifying the later examples, we will 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 id single high incremented, a name in text format and two type fields date, beginning and end, to store the beginning and end of each event, as you can imagine. Now let’s popular 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 are the events that are happening on a given day, right? Let’s assume that return the events on 04/02/2017. Just run the following instruction:

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

Alternatively, it may be used where "2017-02-04" between inicio and fim to produce the same result, leaving the instruction more readable.

Basically we’re telling SQL: From the table eventos, return me the values of id and nome of events with a lower start date than (prior to) 04/02/2017 and an end date greater than (subsequent to) 04/02/2017.

The result? It is shown below:

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

The Signal of equals in comparisons in Where is intended to include the day sought in the crease desired.

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

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

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

Alternatively, it may be used where curdate() between inicio and fim to produce the same result, leaving the instruction more readable.

Thus, the result will be, for today, day 01/02/2017, the following:

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

Cool, but how about we search for all the 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                 |
+----+---------------------+

Recalling that the results presented in the last three instructions, which involve the use of the function curdate() varies according to the day being executed. This way, was presented the results for the day 01/02/2017.

You can still check out all the instructions presented here in operation here. The results of each instruction are presented in separate tables at the bottom of the page.