mysql insert current date, only in insert time

Question

this is the table structure,

id             int
name           varchar
insert_date    date
update_date    TIMESTAMP

Now here the idia is , insert_date will be able to insert by defult current time & the update_date it will insert current time in each edit or insert it will update it.

so how i could make the (insert_date) default value to current time and date but on update it should not change the value of (insert_date) if i keep it TIMESTAMP on update it will change.

insert_date -- insert current time , only in insert just one time

regards

score 4 · Answer 1 · answered Sep 04 '14 at 06:47

If you want to add only time .. you need to use

CURTIME()

AND if you want current date and time...

NOW()

If you want only current date....

CURDATE()

for more here..

The following SELECT statement: SELECT NOW(),CURDATE(),CURTIME()

will result in something like this: NOW() CURDATE() CURTIME() 2008-11-11 12:45:34 2008-11-11 12:45:34

Choose format as you want.

sumit · Answer 2 · 2014-09-04T06:54:00.363

2

use DEFAULT CURRENT_TIMESTAMP for insert

use ON UPDATE CURRENT_TIMESTAMP for update

like below

CREATE TABLE IF NOT EXISTS `datetest` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(100) NOT NULL,
      `timestamps_updated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
      `timestamps_inserted` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' DEFAULT CURRENT_TIMESTAMP,

      PRIMARY KEY (`id`)
    )

edited Sep 04 '14 at 06:54

answered Sep 04 '14 at 06:48

sumit

15,003
12
69
110

I get this error #1067 - Invalid default value for 'timestamps_updated' – Lu Blue Jun 13 '20 at 17:27

score 1 · Answer 3 · edited May 23 '17 at 11:45

1

If you want to keep default current date & time means, you want to change

insert_date data type date to datetime

And insert like

Insert into tbl(insert_date) values(now())

or

$date=date('Y-m-d H:i:s')

Insert into tbl(insert_date) values('$date')

or

Read it

How do you set a default value for a MySQL Datetime column?

edited May 23 '17 at 11:45

Community

1
1

answered Sep 04 '14 at 06:46

Punitha Subramani

1,467
1
8
6

score 0 · Answer 4 · answered Sep 04 '14 at 06:45

0

Use DATETIME type if you want to store Date and time, and you can choose CURRENT_TIMESTAMP as a default value or use NOW() to get current date and time (yyyy-mm-dd Hour:minutes:seconds).

answered Sep 04 '14 at 06:45

brianlasta

304
2
7

SagarPPanchal · Accepted Answer · 2018-07-26T10:30:29.873

-1

you may use NOW() function in you respective fields

edited Jul 26 '18 at 10:30

answered Sep 04 '14 at 06:43

SagarPPanchal

9,839
6
34
62

mysql insert current date, only in insert time

5 Answers5