Let's mess around with dates and times, shall we? One really powerful thing you can do is search and sort by date. Dates are always specified MSB to LSB. Most significant date to least. What that means is, a year is more significant than a month, and a month more than a day.
In MySQL, we always specify dates and times like the following:
2019-12-31 16:19:59Year-Month-Date Hour:Minute:Second
Let's update our first employees table with a started field that indicates when an employee started at the company.
alter table names add started datetime null;mysql> describe names;
+-----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| lastName | varchar(252) | NO | | NULL | |
| firstName | varchar(252) | NO | | NULL | |
| started | datetime | YES | | NULL | |
+-----------+------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)We could also specify that we only want started to be a date, like:
alter table names modify started date null;mysql> describe names;
+-----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| lastName | varchar(252) | NO | | NULL | |
| firstName | varchar(252) | NO | | NULL | |
| started | date | YES | | NULL | |
+-----------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)For now, we'll leave it as a date, because we don't care about what time the person started.
On a side note, you can organize the way your fields are placed. For example, what if we wanted started to show up after id in our table description?
First, let's drop (aka remove) the field:
alter table names drop started;Then we create a query that uses the after keyword and then specify the field we want the field to come after.
alter table names add started date null after id;mysql> describe names;
+-----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| started | date | YES | | NULL | |
| lastName | varchar(252) | NO | | NULL | |
| firstName | varchar(252) | NO | | NULL | |
+-----------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)Now we officially like the organization of our table. Let's move on...
Let's get a look at our names table really quick:
mysql> select * from names;
+----+---------+----------+-----------+
| id | started | lastName | firstName |
+----+---------+----------+-----------+
| 1 | NULL | Smith | Jim |
| 2 | NULL | Rocker | Bob |
+----+---------+----------+-----------+
2 rows in set (0.00 sec)Since we just added the started field, we have NULL for started. Let's update those. We'll assume that Jim and Bob were the founders of the company and they started the same day. So let's use one query to update both dates.
mysql> update names set started='2019-01-01';
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2 Changed: 2 Warnings: 0You'll notice the CLI output 2 rows affected.
And we confirm:
mysql> select * from names;
+----+------------+----------+-----------+
| id | started | lastName | firstName |
+----+------------+----------+-----------+
| 1 | 2019-01-01 | Smith | Jim |
| 2 | 2019-01-01 | Rocker | Bob |
+----+------------+----------+-----------+
2 rows in set (0.01 sec)Let's add a new employee:
insert into names set started=now(), lastName="Kella", firstName="Shella";mysql> select * from names;
+----+------------+----------+-----------+
| id | started | lastName | firstName |
+----+------------+----------+-----------+
| 1 | 2019-01-01 | Smith | Jim |
| 2 | 2019-01-01 | Rocker | Bob |
| 3 | 2019-12-21 | Kella | Shella |
+----+------------+----------+-----------+
3 rows in set (0.00 sec)You'll notice that we used the MySQL function, now(), to fill in the started field. This is one way to timestamp a field, without having to pre-generate a time and date string. now() also covers datetime fields. It will fill in the time as well as the date, essentially.
It seems like even adding started=now() is too much work. Right? MySQL allows us to specify automatically setting timestamps when we insert a row.
Let's alter our table again:
alter table names modify column started timestamp default current_timestamp not null;Or if we want to add a field, instead of modify an already existing field:
alter table names add column started timestamp default current_timestamp not null;mysql> select * from names;
+----+---------------------+----------+-----------+
| id | started | lastName | firstName |
+----+---------------------+----------+-----------+
| 1 | 2019-01-01 00:00:00 | Smith | Jim |
| 2 | 2019-01-01 00:00:00 | Rocker | Bob |
| 3 | 2019-12-21 00:00:00 | Kella | Shella |
+----+---------------------+----------+-----------+
3 rows in set (0.01 sec)You'll notice that the field was changed to include times. That's just part of specifying a field as a timestamp instead of a date. It more mimics the datetime field.
Let's try adding another employee
mysql> insert into names set lastName='Toga', firstName='Sara';
Query OK, 1 row affected (0.02 sec)Notice we didn't set the started field to anything. And we confirm that the timestamp was recorded:
mysql> select * from names;
+----+---------------------+----------+-----------+
| id | started | lastName | firstName |
+----+---------------------+----------+-----------+
| 1 | 2019-01-01 00:00:00 | Smith | Jim |
| 2 | 2019-01-01 00:00:00 | Rocker | Bob |
| 3 | 2019-12-21 00:00:00 | Kella | Shella |
| 4 | 2019-12-21 13:31:39 | Toga | Sara |
+----+---------------------+----------+-----------+
4 rows in set (0.01 sec)Not too shabby.