In MySQL is it possible to save a date and two hours in the same field or column?

Question:

This is one of those questions that come to you when you are responding to situations raised here on Stackoverflow.

Is there the possibility in MySQL to save a date and two different times in the same field?

That is, if I have this:

Fecha inicio: 2017-09-04
Hora  inicio: 15:30:00
Hora  fin   : 18:30:00

Could you keep all of that in one field?

If it could be done, would it be feasible?

In a possible solution I would avoid far-fetched solutions. My question refers to the possibility of a type of data that allows that and that the stored data is what it is , that is, dates and / or times.

Answer:

In MySQL there is no data type that is "date range", which is what you seem to be looking for. In the MySQL documentation you can find the data types and the specific types for date / time which are:

  • DATE
  • TIME
  • DATETIME
  • TIMESTAMP
  • YEAR

Which would all be unitary values ​​and not multiple (which is what you would need). In other database engines you could define your own type for what you are looking for, but MySQL does not have that functionality.

… although perhaps you could simulate it using a VARCHAR (although it would require you to apply functions on the field so it is not very natural or simple) or a JSON type object ( available as of MySQL 5.7.8 ).

MySQL 5.7: The idea would be that you save a JSON with date, start time and end time, and then you access the values ​​to be able to operate on them (it would not be necessary to have special functions or cast values).

Here's an example ( you can run it here ):

CREATE TABLE pruebafechas (
    id INT NOT NULL AUTO_INCREMENT,
    nombre VARCHAR(100),
    valor JSON,
    PRIMARY KEY(id)
);

INSERT INTO pruebafechas(nombre, valor) 
VALUES      ('cita con doctor', '{ "fecha": "2017-09-05", "inicio": "08:00:00", "fin": "10:00:00" }'),
            ('almuerzo', '{ "fecha": "2017-09-05", "inicio": "11:00:00", "fin": "11:30:00" }');

SELECT nombre
FROM   pruebafechas
WHERE  valor->"$.inicio" = "08:00:00";
-- Devuelve "cita con doctor"

SELECT nombre
FROM   pruebafechas
WHERE  HOUR("11:10:00") BETWEEN valor->"$.inicio" AND valor->"$.fin";
-- Devuelve "almuerzo"
Scroll to Top