CODEWITHSUNDEEP
mysql
Reuben Tan
Reuben Tan

Reputation: 81

MySQL column that auto fills TIME when data is inserted in row

I would like to ask if it's possible to create a MySQL table that auto fills a time column whenever a new row is inserted. I have researched through various websites on TIMESTAMPS and ways to only select time from DATETIME, but to no luck, I can't find what I need. I'm using Workbench.

This is my table query

create table dev_monitor(
device_id varchar(50),
power_coms float,
date DATE,
time TIME);

I used a trigger that auto fills dates.

CREATE TRIGGER task_creation_timestamp BEFORE INSERT ON dev_monitor 
FOR EACH ROW
SET NEW.date = NOW();

But now I'm left with the TIME column.

Empty Time Column

This is the query that I'll be using.

insert into dev_monitor(device_id, power_coms)
values('aircond2', '3.5');

Note that I only want to insert the device id and power consumption, and whenever I insert those 2 data, I'm trying to have the table auto fill the date and time, but in separate columns.

Upvotes: 2

Views: 1350

Answers (3)

user7161651
user7161651

Reputation:

You can edit the structure of the table and combine the columns ‘date’ and ‘time’ to a single column and use the datatype ‘DATETIME’ and set the default value to ‘NOW()’ or CURRENT_TIMESTAMP()

Upvotes: 0

Yih Wei
Yih Wei

Reputation: 537

Reuben. You may try on the following codes.

DELIMITER ;;
CREATE TRIGGER device
BEFORE INSERT ON device
FOR EACH ROW
BEGIN
    IF new.date IS NULL THEN
        SET NEW.date = CURDATE();
        SET NEW.time = CURTIME();
    END IF;
END
;;

You can refer further on this link. Refer

Upvotes: 1

Dominik Mayrhofer
Dominik Mayrhofer

Reputation: 491

As shown in this post How do you set a default value for a MySQL Datetime column? you could use a Timestamp. I don't know if it's possible to fill in only the time but you could only SELECT the time later or create a View over it if you don't need the DATE information.

e.g. CREATE VIEW v1 AS SELECT *, TIME(yourDateTime) AS time FROM yourTable;

Upvotes: 1

Related Questions