[mysql] timestamp 타입 주의사항

mysql에서 timestamp 컬럼을 추가할 때 주의할 점이 있다. 

timestamp 컬럼 그 자체로 타입을 정의하면, 밀리초(millisecond)는 나타나지 않는다.

timestamp(1)은  소수점 첫번째자리,

timestamp(2)는 소수점 두번째 자리를 표현한다.

그래서 최대 6자리 까지 정확도를 높일 수 있다.

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

MySQL 5.6.4 and up expands fractional seconds support for TIME, DATETIME, and TIMESTAMP values, with up to microseconds (6 digits) precision:

• To define a column that includes a fractional seconds part, use the syntax type_name(fsp), where type_name is TIME, DATETIME, or TIMESTAMP, and fsp is the fractional seconds precision. For example:

  CREATE TABLE t1 (t TIME(3), dt DATETIME(6));

  The fsp value, if given, must be in the range 0 to 6. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0. (This differs from the standard SQL default of 6, for compatibility with previous MySQL versions.)

• Inserting a TIME, DATE, or TIMESTAMP value with a fractional seconds part into a column of the same type but having fewer fractional digits results in rounding. Consider a table created and populated as follows:

  CREATE TABLE fractest( c1 TIME(2), c2 DATETIME(2), c3 TIMESTAMP(2) );
  INSERT INTO fractest VALUES
  ('17:51:04.777', '2018-09-08 17:51:04.777', '2018-09-08 17:51:04.777');

  The temporal values are inserted into the table with rounding:

  mysql> SELECT * FROM fractest;
  +-------------+------------------------+------------------------+
  | c1          | c2                     | c3                     |
  +-------------+------------------------+------------------------+
  | 17:51:04.78 | 2018-09-08 17:51:04.78 | 2018-09-08 17:51:04.78 |
  +-------------+------------------------+------------------------+