Udemy - The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert를 수강하며 정리하는 글

DATE, TIME, and DATETIME

• DATE : Values With a Date But No Time

  'YYYY-MM-DD' Format

• TIME : Values With a Time But No Date

  'HH:MM:SS' Format

• DATETIME : Values With a Date and Time

  'YYYY-MM'DD HH:MM:SS' Format

이중 DATETIME이 가장 많이 쓰인다고 한다.

mysql> CREATE TABLE people (
    -> name VARCHAR(100),
    -> birthdate DATE,
    -> birthtime TIME,
    -> birthdt DATETIME
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO people(name, birthdate, birthtime, birthdt) 
    -> VALUES('Padma','1983-11-11','10:07:35','1983-11-11 10:07:35');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO people(name, birthdate, birthtime, birthdt) VALUES('Larry','1943-12-12','11:07:35','1943-12-12 11:07:35');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM people;
+-------+------------+-----------+---------------------+
| name  | birthdate  | birthtime | birthdt             |
+-------+------------+-----------+---------------------+
| Padma | 1983-11-11 | 10:07:35  | 1983-11-11 10:07:35 |
| Larry | 1943-12-12 | 11:07:35  | 1943-12-12 11:07:35 |
+-------+------------+-----------+---------------------+
2 rows in set (0.00 sec)

CURDATE(), CURTIME(), NOW()

• CURDATE - gives current date

• CURTIME() - gives current time

• NOW() - gives current datetime

mysql> SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2021-05-07 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 23:13:41  |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2021-05-07 23:13:45 |
+---------------------+
1 row in set (0.00 sec)

테이블에 넣어보자.

mysql> INSERT INTO people(name, birthdate, birthtime,birthdt)     
-> VALUES('Toaster', CURDATE(), CURTIME(), NOW());
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM people;
+---------+------------+-----------+---------------------+
| name    | birthdate  | birthtime | birthdt             |
+---------+------------+-----------+---------------------+
| Padma   | 1983-11-11 | 10:07:35  | 1983-11-11 10:07:35 |
| Larry   | 1943-12-12 | 11:07:35  | 1943-12-12 11:07:35 |
| Toaster | 2021-05-07 | 23:15:55  | 2021-05-07 23:15:55 |
+---------+------------+-----------+---------------------+
3 rows in set (0.01 sec)