[mysql] timestamp 타입 주의사항

DB 2019. 3. 6. 23:57

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 |
+-------------+------------------------+------------------------+

저작자표시비영리동일조건

'DB' 카테고리의 다른 글

[mysql] mysql 5.7에서 Waiting for table metadata lock 재현 및 해결하기 (0)	2019.06.11
Warning: Using a password on the command line interface can be insecure 다른 방법 해결 (0)	2019.04.24
[mysql] timestamp 타입 주의사항 (0)	2019.03.06
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 104 (0)	2019.01.11
[oracle] ORA-01843: not a valid month 해결 하기 (0)	2018.12.03
ORA-00933: SQL 명령어가 올바르게 종료되지 않았습니다 (0)	2018.04.04