Setting a column as timestamp in MySql workbench?

Question

This might be a really elementary question, but I've never created a table with TIMESTAMP() before, and I'm confused on what to put as the parameters. For example, here:

I just randomly put TIMESTAMP(20), but what does the 20 as a parameter signify here? What should be put in here?

I googled the question, but didn't really come up with anything so... Anyway I'm new to sql, so any help would be greatly appreciated, thank you!!

Solution 1

EDIT

As of MySQL 5.6.4, datatype TIMESTAMP(n) specifies n (0 up to 6) decimal digits of precision for fractional seconds.

Before MySQL 5.6, MySQL did not support fractional seconds stored as part of a TIMESTAMP datatype.

Reference: https://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html

---

We don't need to specify a length modifier on a TIMESTAMP. We can just specify TIMESTAMP by itself.

But be aware that the first TIMESTAMP column defined in the table is subject to automatic initialization and update. For example:

create table foo (id int, ts timestamp, val varchar(2));

show create table foo; 

CREATE TABLE `foo` (
`id` INT(11) DEFAULT NULL,
`ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`val` VARCHAR(2) DEFAULT NULL
) 

---

What goes in parens following a datatype depends on what the datatype is, but for some datatypes, it's a length modifier.

For some datatypes, the length modifier affects the maximum length of values that can be stored. For example, VARCHAR(20) allows up to 20 characters to be stored. And DECIMAL(10,6) allows for numeric values with four digits before the decimal point and six after, and effective range of -9999.999999 to 9999.999999.

For other types, the length modifier it doesn't affect the range of values that can be stored. For example, INT(4) and INT(10) are both integer, and both can store the full range of values for allowed for the integer datatype.

What that length modifier does in that case is just informational. It essentially specifies a recommended display width. A client can make use of that to determine how much space to reserve on a row for displaying values from the column. A client doesn't have to do that, but that information is available.

EDIT

A length modifier is no longer accepted for the TIMESTAMP datatype. (If you are running a really old version of MySQL and it's accepted, it will be ignored.)

Solution 2

Thats the precision my friend, if you put for example (2) as a parameter, you will get a date with a precision like: 2015-12-29 00:00:00.00, by the way the maximum value is 6.

Solution 3

This syntax seems to be from old version of MySQL, prior to 4.1. It has been removed completely from 5.5 https://dev.mysql.com/doc/refman/5.0/en/upgrading-from-previous-series.html

So no point in specifying a width here, as it may be ignored. What version are you running?

Solution 4

MySQL 5.7 appears to support this syntax. The argument passed is the precision. TIMESTAMP(3) will allow millisecond precision. 6 is the highest amount of allowed precision.

reference: http://dev.mysql.com/doc/refman/5.7/en/datetime.html

Solution 5

In MySQL workbench 8.0

TIMESTAMP

doesn't work, you need to add wole statement (if u don't want to update timestamp in future)

TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 

than u have e.g :

2020-01-08 19:10:05

but if you want that TIMESTAMP could be modify with the record update than you use :

TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

View more solutions