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

Question

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

Navigation

#1 by (9 votes)

8

This is a question of those that come to you when you are responding to situations raised here in Stackoverflow.

Is there a possibility in MySQL to save a different date and two hours 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 I save all that in one field?

If it could be done, would it be feasible?

In a possible solution, I would avoid elaborate solutions. My question refers to the possibility of a data type that allows that and that the stored data is what it is , that is, dates and / or hours. p>

mysql

asked by A. Cedano 04.09.2017 в 21:35

source

1 answer

How to make a NOT IN in LINQ? Conversion from string DEF to type Date is not valid

score 9 · Accepted 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 specific types for date / time which are:

DATE
TIME
DATETIME
TIMESTAMP
YEAR

That they would all be unitary and not multiple values (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 object (available from MySQL 5.7.8 ).

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

Here is 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"