2

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.

Reuben Tan
  • 81
  • 8

3 Answers3

1

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;

Community
  • 1
  • 1
Dominik Mayrhofer
  • 459
  • 9
  • 19
  • Thanks for your input. Maybe I'll just use timestamp if there's really no way of having a distinct column for time. I checked View over and its something new to me, but doesn't really solve my curiosity. I'll continue checking it out. thx – Reuben Tan May 12 '17 at 06:03
  • with the view over it i meant to create a view that selects the content from your table and changes the content like SELECT *, TIME(yourDateTime) AS time – Dominik Mayrhofer May 12 '17 at 06:15
1

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

Community
  • 1
  • 1
Yih Wei
  • 537
  • 3
  • 21
0

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()

  • True, that is a good way to do it but I was more on having 2 separate columns for date and time respectively. Thank you for the suggestion though! – Reuben Tan May 15 '17 at 00:38
  • No problem at all. Might help someone somewhere. –  May 15 '17 at 06:00