MySQL timestamp/date default value

Question

I have my table "client" in MySQL. I want to set a default date but as I noticed , this isn't possible with "DEFAULT CURDATE()". Some People say it is possible by changing the datatype from last_seen to timestamp. But is it possible to just set the "%d-%m-%Y" or "%Y-%m-%d" as defautlt because with timestamp I also get minutes hours and minutes.

CREATE TABLE IF NOT EXISTS client
(
  pk_macaddr       VARCHAR(17) PRIMARY KEY NOT NULL,
  ipaddress         VARCHAR(15) NOT NULL ,
  hostname         VARCHAR(50) UNIQUE NOT NULL ,
  fk_pk_roomnumber INTEGER NOT NULL ,
  last_seen        DATE ,
  is_online        BOOLEAN default false
);

Related: [Can't default date to CURRENT_TIMESTAMP in MySQL 5.5](https://dba.stackexchange.com/q/132951/2037), and [CURRENT_DATE/CURDATE() not working as default DATE value](https://stackoverflow.com/q/20461030/190597). — unutbu, Mar 03 '19 at 00:41

Nick · Accepted Answer · 2019-03-03T00:46:42.970

Only the TIMESTAMP and DATETIME data types support automatic initialisation and updating (see the manual), in which case you can declare last_seen for example as

last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

To work around the fact that you only want a date, you have a couple of options. You can add a generated column to your table (and use that in SELECT instead of last_seen):

ALTER TABLE client ADD last_seen_date AS (DATE(last_seen))

Or you can create a VIEW:

CREATE VIEW client_view AS
SELECT pk_macaddr, ipaddress, hostname, fk_pk_roomnumber, DATE(last_seen) AS last_seen, is_online
FROM client

MySQL timestamp/date default value

1 Answers1