How to put default value in time field in mysql?

Question

How to put default value in time field in mysql?

Navigation

#1 by (0 votes)
#2 by (0 votes)
#3 by (0 votes)

0

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

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 time of the server, ie current_time exists.

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

Note: I can use trigger or other type of field, 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 a default the server time would be great .

mysql time

asked by anonymous 21.06.2017 / 17:20

3 answers

header bar does not redirect correctly How to run macro with project locked?

score 0 · Answer 1

0

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

Then it would be

Insert INTO table (datetime) values (current_timestamp ());

21.06.2017 / 18:18

score 0 · Answer 2

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 ;

score 0 · Answer 3

You can leave the field as TIMESTAMP and when it's time to use it, format it to just catch the time.

Ex:

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

Time to use:

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