Udemy - The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert를 수강하며 정리하는 글
TIMESTAMP
공식문서
MySQL에서 TIMESTAMP는 another datatype이다.
DATETIME과 TIMESTAMP는 둘다 시간 정보이다. 그러나 두가지 다른점이 있다.
The main difference comes down to the range of times that and date they support.
So if we look at DATETIME it goes back to 1000 and all the way up to year '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
TIMESTAMP on the other hand only goes back to '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.
So that's really important to note if you are working with something where storing dates that may go back very far, TIMESTAMP would not work. Same thing going forward if your're working with where you need things to be further 2038, then you're not going to want to use timestamps as well.
But what we're talking about just saving when something is created or when it's most recently updated we don't need to worry about that being before 1970 or after 2038 because it's right now or when our app is up and running. NOW() is relatively. But basically it's not going to be pass 2038 and we're not changing those dates all that we're going todo is store information.
So, TIMESTAMP is a good data type to use there and you might be wondering why use it in the first place. Why does it exist if there's already DATETIME. The answer is it just takes up less space.
mysql> CREATE TABLE comments(
-> content VARCHAR(100),
-> created_at TIMESTAMP DEFAULT NOW()
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> DESC comments;
+------------+--------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+-------------------+-------------------+
| content | varchar(100) | YES | | NULL | |
| created_at | timestamp | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+--------------+------+-----+-------------------+-------------------+
2 rows in set (0.00 sec)
위의 테이블에 데이터를 넣어보자.
mysql> INSERT INTO comments(content) VALUES('lol what a funny article');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO comments(content) VALUES('I fount this offensive');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM comments;
+--------------------------+---------------------+
| content | created_at |
+--------------------------+---------------------+
| lol what a funny article | 2021-05-08 01:19:29 |
| I fount this offensive | 2021-05-08 01:19:42 |
+--------------------------+---------------------+
2 rows in set (0.00 sec)이런식으로 created_at이나 updated_at등과 함께 NOW()와 함께 데이터가 생성되거나 업데이트 된 시간을 기록할때 자주 쓰인다.
물론 ORDER BY와 함께 쓰일 수 있다.
mysql> INSERT INTO comments(content) VALUES('hihihi');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM comments ORDER BY created_at DESC;
+--------------------------+---------------------+
| content | created_at |
+--------------------------+---------------------+
| hihihi | 2021-05-08 01:21:48 |
| I fount this offensive | 2021-05-08 01:19:42 |
| lol what a funny article | 2021-05-08 01:19:29 |
+--------------------------+---------------------+
3 rows in set (0.00 sec)update됬을 때 자동으로 시간이 바뀌게 하고싶을 수도 있다.
mysql> CREATE TABLE comments2(
-> content VARCHAR(100),
-> changed_at TIMESTAMP DEFAULT NOW() ON UPDATE CURRENT_TIMESTAMP
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> DESC comments2;
+------------+--------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+-------------------+-----------------------------------------------+
| content | varchar(100) | YES | | NULL | |
| changed_at | timestamp | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+------------+--------------+------+-----+-------------------+-----------------------------------------------+
2 rows in set (0.00 sec)여기서 ON UPDATE CURRENT_TIMESTAMP이부분은 row가 UPDATE 될 때 CURRENT_TIMESTAMP(현재 시간)으로 업데이트 해주라는 의미이다. CURRENT_TIMESTAMP가 아니라 NOW()로 써줘도 된다.
mysql> INSERT INTO comments2(content) VALUES('lol what a funny article');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO comments2(content) VALUES('AAAAAAAAAAA');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO comments2(content) VALUES('Hello World!');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM comments2;
+--------------------------+---------------------+
| content | changed_at |
+--------------------------+---------------------+
| lol what a funny article | 2021-05-08 01:31:46 |
| AAAAAAAAAAA | 2021-05-08 01:32:03 |
| Hello World! | 2021-05-08 01:32:10 |
+--------------------------+---------------------+
3 rows in set (0.00 sec)이런 데이터가 있다.
mysql> SELECT * FROM comments2;
+--------------------------+---------------------+
| content | changed_at |
+--------------------------+---------------------+
| lol what a funny article | 2021-05-08 01:31:46 |
| AAAAAAAAAAA | 2021-05-08 01:32:03 |
| Hello World! | 2021-05-08 01:32:10 |
+--------------------------+---------------------+
3 rows in set (0.00 sec)
mysql> UPDATE comments2 SET content = 'HIHI~' WHERE content = 'AAAAAAAAAAA';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM comments2;
+--------------------------+---------------------+
| content | changed_at |
+--------------------------+---------------------+
| lol what a funny article | 2021-05-08 01:31:46 |
| HIHI~ | 2021-05-08 01:34:08 |
| Hello World! | 2021-05-08 01:32:10 |
+--------------------------+---------------------+
3 rows in set (0.00 sec)AAA...를 HIHI~로 업데이트 해줬다. 그리고 changed_at칼럼을 보면 딱히 업데이트 해주지 않았지만 2021-05-08 01:32:03에서 2021-05-08 01:34:08로 바뀐 것을 볼 수 있다.
ORDER BY 해주면 최근에 업데이트 된 순으로 정렬할 수도 있다.
mysql> SELECT * FROM comments2 ORDER BY changed_at DESC;
+--------------------------+---------------------+
| content | changed_at |
+--------------------------+---------------------+
| HIHI~ | 2021-05-08 01:34:08 |
| Hello World! | 2021-05-08 01:32:10 |
| lol what a funny article | 2021-05-08 01:31:46 |
+--------------------------+---------------------+
3 rows in set (0.00 sec)MySQL Datetime, Timestamp 차이에 대해
[MySQL || MariaDB] TIMESTAMP와 DATETIME 차이점 총 정리
