Working with Dates and Times in MySQL

From Techotopia
Jump to: navigation, search
PreviousTable of ContentsNext
MySQL Mathematical FunctionsMySQL Data Aggregation Functions


Purchase and download the full PDF and ePub versions of this MySQL eBook for only $9.99


In this chapter of MySQL Essentials we are going to look at storing dates and times in a database table and also retrieving and manipulating these values.


Contents


Date and Time Formats

<google>ADSDAQBOX_FLOW</google> MySQL supports a number of date and time column formats. These can be summarized as follows:

  • DATE - Stores a date value in the form YYYY-MM-DD. For example 2008-10-23.
  • DATETIME - Stores a date and time value of the form YYYY-MM-DD HH:MM:SS. For example 2008-10-23 10:37:22. The supported range of dates and times is 1000-01-01 00:00:00 all the way through to 9999-12-31 23:59:59
  • TIMESTAMP - Similar to DATETIME with some differences depending on the version of MySQL and the mode in which the server is running.

Creating Date and Time Fields

A table containing DATE and DATETIME columns is created much the same way as any other columns in a table. For example, we can create a new table called orders that contains order number, order item, order date and order delivery columns as follows:

CREATE TABLE `MySampleDB`.`orders` (
  `order_no` INT  NOT NULL AUTO_INCREMENT,
  `order_item` TEXT  NOT NULL,
  `order_date` DATETIME  NOT NULL,
  `order_delivery` DATE  NOT NULL,
  PRIMARY KEY (`order_no`)
)
ENGINE = InnoDB;

The order_date column is a DATETIME field because we want to record the precise time and date that the order was placed. For the delivery date it will be impossible to predict the exact time of day the package will arrive, so we just want to record the date.


Date and Time Formats

Whilst it is most common to store dates using a dash (-) as the delimiter and a colon (:) as the time delimiter it is in fact possible to use any character, or no character between the date and time segments. For example, the following formats all achieve the same result:

2008-10-23 10:37:22
20081023103722
2008/10/23 10.37.22
2008*10*23*10*37*22

As you can see MySQL provides considerable flexibility in how dates and times are formatted.

Date and Time Functions

In addition to providing mechanisms for storing dates and times, MySQL also provides a wide range of functions that can be used to manipulate dates and times. The following table provides a list of the more common functions available for working with times and dates in MySQL:

Function Description
ADDDATE() Add dates
ADDTIME() Add time
CONVERT_TZ() Convert from one timezone to another
CURDATE() Returns the current date
CURTIME() Returns the current system time
DATE_ADD() Add two dates
DATE_FORMAT() Format date as specified
DATE_SUB() Subtract two dates
DATE() Extract the date part of a date or datetime expression
DATEDIFF() Subtract two dates
DAYNAME() Returns the name of the weekday
DAYOFMONTH() Returns the day of the month (1-31)
DAYOFWEEK() Returns the weekday index of the argument
DAYOFYEAR() Returns the day of the year (1-366)
EXTRACT Extract part of a date
FROM_DAYS() Convert a day number to a date
FROM_UNIXTIME() Format date as a UNIX timestamp
GET_FORMAT() Returns a date format string
HOUR() Extract the hour
LAST_DAY Returns the last day of the month for the argument
MAKEDATE() Create a date from the year and day of year
MAKETIME MAKETIME()
MICROSECOND() Returns the microseconds from argument
MINUTE() Returns the minute from the argument
MONTH() Returns the month from the date passed
MONTHNAME() Returns the name of the month
NOW() Returns the current date and time
PERIOD_ADD() Add a period to a year-month
PERIOD_DIFF() Returns the number of months between two periods
QUARTER() Returns the quarter from a date passed as an argument
SEC_TO_TIME() Converts seconds to 'HH:MM:SS' format
SECOND() Returns the second (0-59)
STR_TO_DATE() Convert a string to a date
SUBTIME() Subtract times
SYSDATE() Returns the time at which the function executes
TIME_FORMAT() Format as time
TIME_TO_SEC() Returns the argument converted to seconds
TIME() Extract the time portion of the expression passed as an argument
TIMEDIFF() Subtract time
TIMESTAMP() With a single argument, this function returns the Date or the Datetime expression. With two arguments, the sum of the arguments is returned
TIMESTAMPADD() Add an interval to a datetime expression
TIMESTAMPDIFF() Subtract an interval from a datetime expression
TO_DAYS() Returns the date argument converted to days
UNIX_TIMESTAMP() Returns a UNIX timestamp to a format acceptable to MySQL
UTC_DATE() Returns the current Universal Time (UTC) date
UTC_TIME() Returns the current Universal Time (UTC time
UTC_TIMESTAMP() Returns the current Universal Time (UTC) date and time
WEEK() Returns the week number
WEEKDAY() Returns the weekday index
WEEKOFYEAR() Returns the calendar week of the date (1-53)
YEAR() Returns the year
YEARWEEK() Returns the year and week

A useful point to note about these functions is that you can easily experiment without writing any data to a table. For example:

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2007-10-23 11:46:31 |
+---------------------+
1 row in set (0.00 sec)

To try out a combination of functions in a single statement (for example to find the day of the week):

mysql> SELECT MONTHNAME(NOW());
+------------------+
| MONTHNAME(NOW()) |
+------------------+
| October          |
+------------------+
1 row in set (0.00 sec)

Inserting Date and Time Values into Table Columns

Now that we have looked at how dates and times are formatted in MySQL and also explored the vast array of functions that are available to manipulate date and time values it is now time to look at how to insert date and time values into table. To demonstrate this concept we will continue to use the orders table we created at the beginning of this chapter.

We will begin by adding a new order row. The order_no field will auto increment so we need to provide an order_item, an order date and a delivery date. The order date is the time that we place the order, so we can use the NOW() function to place the current date and time into the row.

The delivery date will be some period of time after the order date so we can calculate this using the DATE_ADD() function which takes as arguments the start date (in our case NOW()) followed by the INTERVAL (in our case 14 days). For example:

INSERT INTO orders (order_item, order_date, order_delivery) 
VALUES ('iPhone 8Gb', NOW(), DATE_ADD(NOW(), INTERVAL 14 DAY));

The above statement will create an order for the specified item with the statement execution date and time as the order date and a date two weeks into the future as the delivery date:

mysql> SELECT * FROM orders;
+----------+------------+---------------------+----------------+
| 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)

Similarly we could order an item with a two month delivery time:

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)

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:

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)

Similarly we can use the BETWEEN clause to lists deliveries that fall between two specific dates. For example:

mysql> SELECT * FROM orders WHERE order_delivery BETWEEN '2007-12-01' AND '2008-01-01';
+----------+----------------+---------------------+----------------+
| order_no | order_item     | order_date          | order_delivery |
+----------+----------------+---------------------+----------------+
|        2 | ipod Touch 4Gb | 2007-10-23 11:51:09 | 2007-12-23     |
+----------+----------------+---------------------+----------------+
1 row in set (0.03 sec)

Summary

In this chapter we have covered the basics of storing, retrieving 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.

Purchase and download the full PDF and ePub versions of this MySQL eBook for only $9.99


PreviousTable of ContentsNext
MySQL Mathematical FunctionsMySQL Data Aggregation Functions