How to put default value in time field in mysql?

Question

I searched the web but I did not find anything specific for time field in mysql, only date and timestamp, I am creating a field "hora_emissao" type "time", because I want to store only the time in this field, the line would be the following:

ADD COLUMN `hora_emissao`  time NOT NULL DEFAULT CURRENT_TIME;

But I get the following message:

[Err] 1064 - You have an error in your SQL syntax; check the manual that Corresponds to your Mysql server version for the right syntax to use near 'CURRENT_TIME

If we give the command: SELECT CURRENT_TIME we see that it shows the server time, or current_time exists.

What is the correct way to create a time field with default value in mysql?

Note: I can use Trigger or other field type, but the idea is to simplify using the default function and use a same time field, since I only need the time, if there was how to leave default the server time would be great.

Answer 1

You can leave the field as TIMESTAMP and at the time of using it, format to catch just the time.

Ex:

CREATE TABLE `suaTabela` (
  `datahora` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
)

At the time of using:

SELECT DATE_FORMAT(datahora,"%H:%i:%s") AS hora FROM suaTabela

Answer 2

Create your hourly field as datetime, and in the Insert query, use the current_timestamp function().

Then it would be

Insert INTO table (issuing time) values(current_timestamp());

Answer 3

You can create a TRIGGER to insert the current time in your field:

DELIMITER $$

CREATE
    TRIGGER horaatual BEFORE INSERT ON TIME
    FOR EACH ROW BEGIN
       SET NEW.hora=CURTIME();
    END$$

DELIMITER ;