Open main menu

Changes

Working with Dates and Times in MySQL

1,868 bytes added, 15:55, 23 October 2007
→‎Inserting Date and Time Values into Table Columns
1 row in set (0.00 sec)
</pre>
 
Similarly we could order an item with a two month delivery time:
 
<pre>
mysql> INSERT INTO orders (order_item, order_date, order_delivery) VALUES ('ipod Touch 4Gb', NOW(), DATE_ADD(NOW(), INTERVAL 2 MONTH));
Query OK, 1 row affected (0.00 sec)
 
mysql> SELECT * FROM orders;
+----------+----------------+---------------------+----------------+
| order_no | order_item | order_date | order_delivery |
+----------+----------------+---------------------+----------------+
| 1 | iPhone 8Gb | 2007-10-23 11:37:55 | 2007-11-06 |
| 2 | ipod Touch 4Gb | 2007-10-23 11:51:09 | 2007-12-23 |
+----------+----------------+---------------------+----------------+
2 rows in set (0.00 sec)
</pre>
 
== Retrieving Data Based on Date and Time Criteria ==
 
As with all data retrievals in MySQL, it is possible to filter extracted data based on dates and times. For example, we could retrieve only those orders scheduled for delivery in November:
 
<pre>
mysql> SELECT * FROM orders WHERE MONTHNAME(order_delivery) = 'November';
+----------+------------+---------------------+----------------+
| order_no | order_item | order_date | order_delivery |
+----------+------------+---------------------+----------------+
| 1 | iPhone 8Gb | 2007-10-23 11:37:55 | 2007-11-06 |
+----------+------------+---------------------+----------------+
1 row in set (0.00 sec)
</pre>
 
== Summary ==
 
In this chapter we have covered the bacis of storing, retirieving and manipulating dates and times in MySQL. We have covered the formats used to define dates and time and listed the vast array of functions provided by MySQL to manipulate date and time values. Finally we looked at some examples of data insertion and retrieval. Remember that the SELECT statement can be used to experiment with MySQL date and time functions so feel free to explore.