How to create mysql table with column timestamp default current_date?

Question

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

I try

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 is writting 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 to uniques only date not with full time info...

can you help me?

This is the default behaviour of a TIMESTAMP column. You don't actually need to specify DEFAULT in this case. — eggyal
– eggyal, Commented Dec 22, 2015 at 14:32
TIMESTAMP and DATE do not have the same format, Timestamp is a number while date is a formatted date. Choose one or the other. — Anton
– Anton, Commented Dec 22, 2015 at 14:37
Why don't you try 'date' DATE NOT NULL DEFAULT CURRENT_DATE() if you only need year, month and day. — Divyanshu Kushwaha
– Divyanshu Kushwaha, Commented Jul 28, 2017 at 6:17

Saharsh Shah · Accepted Answer · 2015-12-22 14:30:46Z

46

Use CURRENT_TIMESTAMP function instead of CURRENT_DATE() function

Try this:

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;

answered Dec 22, 2015 at 14:30

Saharsh Shah

29.1k9 gold badges53 silver badges85 bronze badges

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

3 Comments

maia bardavelidze Over a year ago

no CURRENT_TIMESTAMP is setting full time informations with hour,minutes and secunds too. I need only year,month and day

Redbeard011010 Over a year ago

One option is to set it up as a timestamp, as shown, but when you actually retrieve the data, just cast it as a date, which will truncate the hours, minutes and seconds. SELECT DATE(date) will return it in the format you appear to want, while still preserving the behavior you want for the column.

maia bardavelidze Over a year ago

I understand but I need to unique IP with year, month and date so if I unique it as timestamp after 2-3 second ago it will insert again new row... I need unique becouse after refresh if IP is exists today it must not write in table so becouse I need only date format

J.P.A · Accepted Answer · 2015-12-22 14:35:28Z

0

you just have to replace CURRENT_DATE() by NOW() in your query. I tried it and it's look ok.

answered Dec 22, 2015 at 14:35

J.P.A

1051 silver badge6 bronze badges

1 Comment

Saharsh Shah Over a year ago

NOW() will be consider as CURRENT_TIMESTMAP in mysql

omostan · Accepted Answer · 2017-11-04 08:18:11Z

0

This may be a little late but I think it might be helpful to someone else.

My approach has been to use getdate() like this:

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

Where [TimeStamp] is the column in question.

The result is for example: 2017-11-02 11:58:34.203

To trim this, I use the following

declare @mydate datetime

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

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

This final result is: 2017-11-02 11:58:34

You can actually set this in MSSQL Management Studio

edited Nov 4, 2017 at 8:18

answered Nov 4, 2017 at 8:13

omostan

8888 silver badges9 bronze badges

Collectives™ on Stack Overflow

How to create mysql table with column timestamp default current_date?

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related