Date and time processing functions and numerical processing function examples (Usage of data processing functions 2)

Date and time processing functions

Date and time are stored in corresponding data types and special formats so that they can be sorted or filtered quickly and effectively, and save physical storage space .

Generally, applications do not use a format for storing dates and times, so date and time functions are always used to read and process these values. For this reason, date and time functions play an important role in the MySQL language.

The following table lists some commonly used date and time processing functions:

This is a good time to review where to filter data. So far, we have filtered data using where clauses that compare numbers and text, but data often needs to be filtered by date. Filtering by date requires some additional considerations and the use of special MySQL functions.

The first thing to note is the date format used by MySQL. Whenever you specify a date, whether inserting or updating a table value or filtering with a where clause, the date must be in the format yyyy-mm-dd. Therefore, September 1, 2005, is given as 2005-09-01. Although other date formats may work, this is the preferred date format because it excludes ambiguity (e.g., 04/05/06 is May 4, 2006 or April 5, 2006 or May 6, 2004 or...).

You should always use 4-digit years. 2-digit years are supported. MySQL handles 00-69 as 2000-2069 and 70-99 as 1970-1999. Although they may be intended years, using full 4-digit years is more reliable because MySQL does not have to make any assumptions.

So a basic date comparison should be simple:

Input:

select cust_id,order_num from orders where order_date = '2005-09-01';

Output:

Analysis: This select statement runs normally. It retrieves an order record whose order_date is 2005-09-01.

But, is using where order_date = '2005-09-01' reliable? The data type of order_date is datetime. This type stores date and time values. The values ​​in the sample table all have a time value of 00:00:00, but in practice this is likely not always the case. What if I store the order date with the current date and time? For example, if the stored order_date value is 2005-09-01 11:30:05, then where order_date = '2005-09-01' fails. Even if given a row with that date, it won't be retrieved because the where match fails.

The solution is to instruct MySQL to compare the given date only to the date part in the column, rather than comparing the given date to the entire column value. For this purpose, the date() function must be used. Date(order_date) instructs MySQL to extract only the date part of the column. A more reliable select statement is:

Input:

select cust_id,order_num from orders where Date(order_date) = '2005-09-01';

If all you want is the date, then using Date() is A good practice, even if you know that the corresponding column only contains dates. This way, if for some reason there are date and time values ​​in the table later, your SQL code doesn't have to change. Of course, there is also a Time() function, which should be used when you just want the time.

Date() and Time() were first introduced in MySQL4.1.1.

After you know how to test for equality with dates, the use of other operators will be clear.

However, there is another kind of date comparison that needs to be explained. What if you want to retrieve all orders placed in September 2005? A simple equality test won't work because it also has to match the number of days in the month. There are several solutions, one of which looks like this:

Input:

select cust_id,order_num from orders where Date(order_date) between '2005-09-01' and  '2005-09-30';

Output:

Analysis: where, between The operator is used to define 2005-09-01 and 2005-09-30 as a date range to be matched.

Numerical processing function

Numeric processing function only processes numerical data. These functions are generally used primarily for algebraic, trigonometric, or geometric operations, and therefore are not used as frequently as string or date-time processing functions.

Ironically, among the functions of the major DBMS, numeric functions are the most consistent and uniform functions. The following table lists some commonly used numerical processing functions:

The above is the detailed content of Date and time processing functions and numerical processing function examples (Usage of data processing functions 2). For more information, please follow other related articles on the PHP Chinese website!