Save time and date in separate columns in MySQL automatically

Question

Save time and date in separate columns in MySQL automatically

Navigation

#1 by (0 votes)

0

I need to save the current time and date separately when the query is executed automatically but I am not sure how I can do this and if it is the correct way I do it.

CREATE TABLE 'prestamo_equipos'.'prestamo_usuarios' (
  'alumno_matricula' INT NOT NULL,
  'id_equipo' VARCHAR(45) NOT NULL,
  'fecha' DATE NOT NULL DEFAULT CURDATE(),
  'hora' DATE NOT NULL DEFAULT CURTIME(),
  PRIMARY KEY ('alumno_matricula', 'id_equipo'));

mysql

asked by AlexCs 09.11.2018 в 04:11

source

1 answer

pd.cut to establish quintiles based on the filtering of a field SQL Server query - List best clients

score 0 · Answer 1

You can not use functions as default values for a field. The only exception seems to be with fields of type TIMESTAMP :

'ts' TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

As you say you want to separate the date of the time in two fields, you will have to use a trigger:

CREATE TRIGGER 'default_date_and_time' BEFORE INSERT ON 'prestamo_usuarios' FOR EACH ROW
BEGIN
    SET NEW.fecha = curdate();
    SET NEW.hora = curtime();
END

In addition to removing the condition of NOT NULL DEFAULT CURDATE() and NOT NULL DEFAULT CURTIME()