Is there a "time" type that represents such that HH: mm in SQL?
I am a beginner of SQL and I am creating a table , my doubt is that I have defined my start and time as VARCHAR2 to introduce a time , which correspond to a class(of a gym), would it be more correct to create it with some type time , or with with VARCHAR2 and introduce 2]}insert into?
CREATE TABLE CLASES(
OID_CL SMALLINT,
nombre VARCHAR2(20),
horaInicio VARCHAR2(10),
horaFin VARCHAR2(10),
disponibilidadClase VARCHAR2(20),
codigo VARCHAR2(6),
PRIMARY KEY(OID_CL),
FOREIGN KEY(codigo) REFERENCES REGISTRO_CLASES
);
2 answers
, Theoretically, a data type time is more correct that a varchar2 because it will better represent the data internally in the database and is going to give you the flexibility to use some tools that may be convenient (such as functions to manipulate time as DATEDIFF or DATEADD)
The question you should ask yourself is " what am I going to use those fields for?". If you are going to use them to perform time and/or date operations (calculate the time that the class lasts or if there is conflict between classes), it definitely uses a time. If you are only going to use them to display them on the screen, you really do not care what type of data you choose (although technically time would be the right thing, and surely there will come a time when it will be more convenient).
If you are going to use only hours, minutes, seconds and / or nanoseconds, use time, after all, although the difference is minimal, large-scale queries will always be slower when you have leftover data (the date in this case).
Take a look at the Official Documentation and you can see the differences. For starters time has more nanosecond accuracy than datetime. In turn, on the other hand, there is datetime2, which extends a normal datetime with the precision of a time in ns.