I want to update only date when data comes to mysql

Question

CREATE TABLE `tbl_attendance` (
  `att_id` int(11) NOT NULL,
  `rfid_uid` varchar(255) NOT NULL,
  `punch_time` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

This is working fine with timestamp but i want to take only date and time separately in 2 columns. In one there will be only date and in other there will be time. I am working on arduino attendance system when an rfid picks a card and entering the attendance it should mark date and time separately not like timestamp Thanks

Answer 1

Only TIMESTAMP columns can use the ON UPDATE feature to automatically update.

If you're using MySQL 5.7 you can add generated columns that hold the date and time.

CREATE TABLE `tbl_attendance` (
  `att_id` int(11) NOT NULL,
  `rfid_uid` varchar(255) NOT NULL,
  `punch_timestamp` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  punch_date DATE AS (DATE(punch_timestamp)),
  punch_time TIME AS (TIME(punch_timestamp))
)