Till now we've dealt with text (varchar) and numbers (int) data types. To understand date type, we'll create one more table.
Download employee_per.dat file below and follow the instructions. The file contain the CREATE table command as well as the INSERT statements.
Download the file called employee_per.dat (4.38 kb).
These instructions are a repeat of those in session 6 and 8.
Windows system
1). Move the file to c:\mysql\bin.
2). Issue the command at DOS prompt.
dosprompt> mysql employees <employee_per.dat
3). Start mysql client program and check if the table has been created using SHOW TABLES; command.
Linux system
1). Move to the directory that contains the downloaded files.
2). At the prompt, type the command below:
$prompt> mysql employees <employee_per.dat -u your_username -p
3). Check if the table has been created through SHOW TABLES; command in mysql client program.
Here is the view of the new table, employee_per: db2.htm (4.17 kb)
The details of the table can be displayed with DESCRIBE command.
mysql> DESCRIBE employee_per;
+————+——————+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+————+——————+——+—–+———+——-+
| e_id | int(10) unsigned | | PRI | 0 | |
| address | varchar(60) | YES | | NULL | |
| phone | int(11) | YES | | NULL | |
| p_email | varchar(60) | YES | | NULL | |
| birth_date | date | YES | | NULL | |
| sex | enum('M','F') | YES | | NULL | |
| m_status | enum('Y','N') | YES | | NULL | |
| s_name | varchar(40) | YES | | NULL | |
| children | int(11) | YES | | NULL | |
+————+——————+——+—–+———+——-+
9 rows in set (0.01 sec)
Notice that column birth_date has date as column type. I've also introduced another column type ENUM, which we'll discuss later.
e-id: are employee ids, same as that in table employee_data
address: Addresses of employees
phone: Phone numbers
p_email: Personal email addresses
birth_date: Birth dates
sex: The sex of the employee, Male or Female
m_status: Marital status,Yes or No.
s_name: Name of Spouse (NULL if employee is unmarried)
children: Number of children (NULL if employee is unmarried)
MySQL dates are ALWAYS represented with the year followed by the month and then the date. Often you'll find dates written as YYYY-MM-DD, where YYYY is 4 digit year, MM is 2 digit month and DD, 2 digit date. We'll look at DATE and related column types in the session on column types.
Operations on Date
Date column type allow for several operations such as sorting, testing conditions using comparision operators etc.
Using = and != operators on dates
select p_email, phone
from employee_per
where birth_date = '1969-12-31';
+—————————+———+
| p_email | phone |
+—————————+———+
| killeratlarge@elmmail.com | 6666666 |
+—————————+———+
1 row in set (0.00 sec)
Note: MySQL requires the dates to be enclosed in quotes.
Using >= and <= operators
select e_id, birth_date
from employee_per where
birth_date >= '1970-01-01';
+——+————+
| e_id | birth_date |
+——+————+
| 1 | 1972-03-16 |
| 4 | 1972-08-09 |
| 5 | 1974-10-13 |
| 8 | 1975-01-12 |
| 17 | 1970-04-18 |
| 18 | 1973-10-09 |
| 19 | 1973-01-20 |
+——+————+
7 rows in set (0.00 sec)
Specifying date ranges in MySQL
select e_id, birth_date
from employee_per where
birth_date BETWEEN
'1969-01-01' AND '1974-01-01';
+——+————+
| e_id | birth_date |
+——+————+
| 1 | 1972-03-16 |
| 4 | 1972-08-09 |
| 6 | 1969-12-31 |
| 17 | 1970-04-18 |
| 18 | 1973-10-09 |
| 19 | 1973-01-20 |
+——+————+
6 rows in set (0.00 sec)
select e_id, birth_date
from employee_per where
birth_date >= '1969-01-01'
AND birth_date <= '1974-01-01';
+——+————+
| e_id | birth_date |
+——+————+
| 1 | 1972-03-16 |
| 4 | 1972-08-09 |
| 6 | 1969-12-31 |
| 17 | 1970-04-18 |
| 18 | 1973-10-09 |
| 19 | 1973-01-20 |
+——+————+
6 rows in set (0.00 sec)
Assignments:
1.List all employee ids and birth dates who were born before 1965.
2.Display Ids and birth dates of employees born in and between 1970 and 1972. (This is tricky… you need to give it a little thought)
Possible Answers:
1.mysql> select e_id, birth_date
-> from employee_per
-> where birth_date >= '1970-01-01'
-> and birth_date <= '1973-01-01';
+——+————+
| e_id | birth_date |
+——+————+
| 1 | 1972-03-16 |
| 4 | 1972-08-09 |
| 17 | 1970-04-18 |
+——+————+
3 rows in set (0.01 sec)
2.We use 1973 in the condition and NOT 1972.
mysql> select e_id, birth_date
-> from employee_per
-> where birth_date >= '1970-01-01'
-> and birth_date <= '1973-01-01';
+——+————+
| e_id | birth_date |
+——+————+
| 1 | 1972-03-16 |
| 4 | 1972-08-09 |
| 17 | 1970-04-18 |
+——+————+
3 rows in set (0.01 sec)