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

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

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

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

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

MySQL 5.6.4 and up expands fractional seconds support for TIMEDATETIME, 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 TIMEDATETIME, 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 TIMEDATE, 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) );
    ('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 |

Posted by 김용환 '김용환'

댓글을 달아 주세요