Home / Questions / 20233
Accepted
RoyalUp
Asked: 2020-08-12 02:34:20 +0800 CST 2020-08-12 02:34:20 +0800 CST

Is there a type "time" that represents such that HH:mm in SQL?

  • 772

I am a SQL beginner and I am creating a table, my doubt is that I have defined my StartTime and EndTime as VARCHAR2 to introduce a time, which correspond to some classes (of a gym), that it would be more correct to create it with some type time , or with VARCHAR2 and introduce StartTime and EndTime to each class manually with an 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
);
sql

2 Answers

  1. Best Answer

    Theoretically, a data type timeis more correct than a varchar2because it will better represent the data internally in the database and it will give you the flexibility to use some tools that may be convenient (such as functions to manipulate time like DATEDIFFor DATEADD)

    The question you need to 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 how long the class lasts or if there is a conflict between classes), definitely use a time. If you are only going to use them to display them on the screen, the type of data you choose is really indifferent to you (although technically timeit would be correct, and there will surely come a time when it will be more convenient for you).

    • 2

  2. If you are going to use only hours, minutes, seconds and/or nanoseconds, use time , after all, despite the fact that the difference is minimal, on a large scale queries will always be slower when you have extra data (the date in this case).

    Take a look at the official documentation and you can see the differences. For starters, time has more precision in nanoseconds than datetime. In turn, on the other hand, there is datetime2, which extends a normal datetime to the precision of a time in ns.

    • 1