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.
These instructions are a repeat of those in session 6 and 8.
Windows system
- Move the file to c:\mysql\bin.
- Issue the command at DOS prompt.
dosprompt> mysql employees <employee_per.dat
- Start mysql client program and check if the table has been created using SHOW TABLES; command.
Linux system
- Move to the directory that contains the downloaded files.
- At the prompt, type the command below:
$prompt> mysql employees <employee_per.dat -u your_username -p
- Check if the table has been created through SHOW TABLES; command in mysql client program.
Click here for viewing this new table, employee_per.
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)
Characteristics of MySQL Date
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 comparison 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)
