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, and CURRENT_DATE/CURDATE() not working as default DATE value. — unutbu
– unutbu, Commented Mar 3, 2019 at 0:41

Nick · Accepted Answer · 2019-03-03 00:46:42Z

2

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

edited Mar 3, 2019 at 0:46

answered Mar 3, 2019 at 0:39

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL timestamp/date default value

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related