PLEASE support FRACTIONAL seconds~!

[expired user #7857]'s profile image [expired user #7857] posted 10 years ago in General Permalink
# MySQL 5.6.4 and up expands fractional seconds support for TIME, DATETIME, and TIMESTAMP values, with up to microseconds (6 digits) precision:
# http://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html

# also default date and time types
> If a TIMESTAMP or DATETIME column definition includes an explicit fractional seconds precision value anywhere, the same value must be used throughout the column definition. This is permitted:

CREATE TABLE t1 (
ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
);
This is not permitted:

CREATE TABLE t1 (
ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(3)
);

# http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html
ansgar's profile image ansgar posted 10 years ago Permalink
Did you test that? HeidiSQL already supports microseconds in date/time based columns. See the news about that.
[expired user #8155]'s profile image [expired user #8155] posted 10 years ago Permalink
I'm having problems creating or editing a table that contains DEFAULT CURRENT_TIMESTAMP(N) using the Create New Table and Edit Table feature.

As noted by the OP, if you want to have TIMESTAMP(N) column with a default value you have to add the (N) to the DEFAULT value such as DEFAULT CURRENT_TIMESTAMP(N)

The Create New Table and Edit Table tab does not do this. So I tried using "Custom" option but then I get the following error:
SQL Error (1067):Invalid default value for 'MyDateTime'

When I look at the generated SQL I see:
CREATE TABLE `new_table5` (
`MyDateTime` TIMESTAMP(3) NOT NULL DEFAULT 'CURRENT_TIMESTAMP(3)',
PRIMARY KEY (`MyDateTime`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;

Notice the quotes around 'CURRENT_TIMESTAMP(3)'

If I remove the quotes and just run
CREATE TABLE `new_table5` (
`MyDateTime` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (`MyDateTime`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;

Then it works
[expired user #8155]'s profile image [expired user #8155] posted 10 years ago Permalink
Should I create another topic for this bug report?
[expired user #8155]'s profile image [expired user #8155] posted 9 years ago Permalink
Bump

Please login to leave a reply, or register at first.