How to create time attribute in SQL?

Question

I tried to create a table this way:

create table attendance (
      userId char(10) primary key not null, 
      name varchar(35) not null, 
      date_attendance date not null, 
      start_time timestamp 'HH24:MI:SS', 
      finish_time timestamp 'HH24:MI:SS'
);

Am I right about creating the time fields this way or there is some better option?

Answer 1

It is pretty right and one thing you can do is while providing a date set to not null you can also provide a default date to it by assigning getDate or getUTcDate() functions to them(depending on the app requirement)

Answer 2

What is the HH24:MM:SS? That looks like an Oracle/Postgres argument to the TO_CHAR() function.

Presumably, you want two different times, and MySQL has a data type for that:

create table attendance (
      userId char(10) primary key not null, 
      name varchar(35) not null, 
      date_attendance date not null, 
      start_time time, 
      end_time time
);

You are confusing timestamp and time. You use timestamp when you want to include time zone information in the column values. I am guessing that timezone information is not important for your application.

This imposes the strict requirement that each attendance record is on a single day. An alternative is just to use datetime for both:

create table attendance (
      userId char(10) primary key not null, 
      name varchar(35) not null, 
      start_time datetime, 
      end_time datetime
);

This would allow the end_time to pass midnight -- something that might be useful at some point.

In addition, I would recommend using an auto-incremented integer for the primary key:

create table attendance (
      userId unsigned auto_increment primary key,
      name varchar(35) not null, 
      start_time datetime, 
      end_time datetime
);

Finally, when you want to format a date/time/datetime as a string, use date_format().

Answer 3

Timestamp will hold both date and time. There is data type time as well, which might be better suited for your use case.

CREATE TABLE attendance (
      userId char(10) primary key not null, 
      name varchar(35) not null, 
      date_attendance date not null, 
      start_time time,
      finish_time time
);