Geek Answers Handbook

How to create mysql table with default timestamp column value current_date?

I need to create mysql table with default value for column CURRENT_DATE ()

I'm trying to

DROP TABLE IF EXISTS `visitors`; CREATE TABLE `visitors` ( `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `ip` VARCHAR(32) NOT NULL, `browser` VARCHAR(500) NOT NULL, `version` VARCHAR(500) NOT NULL, `platform` ENUM('w','l','m') NOT NULL, `date` TIMESTAMP NOT NULL DEFAULT CURRENT_DATE(), PRIMARY KEY (`id`), UNIQUE KEY `person` (`ip`,`date`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

but it causes an error

 Query: CREATE TABLE `visitors` ( `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `ip` VARCHAR(32) NOT NULL, `browser` VARCHAR(500) NO... Error Code: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CURRENT_DATE() NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `person` (`ip`,`date`) ) ENGINE=INNODB AUTO_INCREMENT=1' at line 7 Execution Time : 0 sec Transfer Time : 0 sec Total Time : 0.063 sec

what is the problem?

I need only once not with full time information ...

Could you help me?

+6
source share
3 answers

Use CURRENT_TIMESTAMP instead of the CURRENT_DATE () function

Try the following:

 DROP TABLE IF EXISTS `visitors`; CREATE TABLE `visitors` ( `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `ip` VARCHAR(32) NOT NULL, `browser` VARCHAR(500) NOT NULL, `version` VARCHAR(500) NOT NULL, `platform` ENUM('w','l','m') NOT NULL, `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `person` (`ip`,`date`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
+14
source

you just need to replace CURRENT_DATE () with NOW () in your query. I tried and everything looks fine.

0
source

It may be a little late, but I think it might be useful to someone else.

My approach was to use getdate () as follows:

[TimeStamp] [datetime] NOT NULL CONSTRAINT [DF_myTable_TimeStamp] DEFAULT (getdate ()).

Where [TimeStamp] is the column in question.

Result, for example: 2017-11-02 11: 58: 34.203

To crop this, I use the following

@Mydate datetime declaration

set @mydate = '2017-11-02 11: 58: 34.203'

SELECT try_convert (nvarchar (20), @mydate, 120)

This end result: 2017-11-02 11:58:34

You can install this in MSSQL Management Studio

0
source

More articles:


All Articles