A detailed explanation of the date and time functions in MySQL built-in functions

The following example uses the time function. The following query selects all records with Date_col values for the last 30 days:

mysql>　SELECT　something　FROM　tbl_name
　　->　WHERE　DATE_SUB(CURDATE(),INTERVAL　30　DAY)　<=　date_col;

Note that this query can also select a future date record.

Functions used for date values typically accept time-date values and ignore the time portion. Functions that are used for time values usually accept the time date value and ignore the date part.

Functions that return their current date or time are evaluated at the beginning of each query execution. This means that in a single query, multiple accesses to a function such as now () will always get the same result (not our purpose, and a single query also includes calls to the stored program or trigger and all subroutines called by the program/trigger). This principle also applies to curdate (), Curtime (), Utc_date (), Utc_time (), Utc_timestamp (), and all functions that have the same meaning as them.

The Current_timestamp (), Current_time (), current_date (), and From_unixtime () functions return the values that are attached to the current time zone, which can be used as a value for Time_zone system variables. In addition, Unix_timestamp () assumes that the parameter is a time date value for the current time zone.

The scope of the return value in the discussion of the following function will request a full date. A function that extracts part of a date value may return 0 if a date is a value of "0" or an incomplete date such as ' 2001-11-00 '. For example, DayOfMonth (' 2001-11-00 ') will return 0.

Adddate (Date,interval expr type) adddate (expr,days)

When activated by the interval format of the second parameter, adddate () is synonymous with date_add (). The correlation function subdate () is a synonym for Date_sub (). For information on the interval parameter, see the discussion about Date_add ().

mysql>　SELECT　DATE_ADD('1998-01-02',　INTERVAL　31　DAY);
->　'1998-02-02'
mysql>　SELECT　ADDDATE('1998-01-02',　INTERVAL　31　DAY);
->　'1998-02-02'

If the days argument is only an integer value, MySQL 5.1 adds it as a day value to expr.

mysql>　SELECT　ADDDATE('1998-01-02',　31);
->　'1998-02-02'

Addtime (EXPR,EXPR2)

Addtime () adds expr2 to expr and then returns the result. Expr is a time or time-date expression, and expr2 is a time expression.

mysql>　SELECT　ADDTIME('1997-12-31　23:59:59.999999',
->　　　　　　　　　　　　　　　　'1　1:1:1.000002');
->　'1998-01-02　01:01:01.000001'
mysql>　SELECT　ADDTIME('01:00:00.999999',　'02:00:00.999998');
->　'03:00:01.999997'

Convert_tz (Dt,from_tz,to_tz)

Convert_tz () Moves the time date value DT from From_tz to the timezone given by To_tz and returns the resulting value.

In the conversion from From_tz to UTC, the value is beyond the supported range of the TIMESTAMP type, then the conversion does not occur.

mysql>　SELECT　CONVERT_TZ('2004-01-01　12:00:00','GMT','MET');
->　'2004-01-01　13:00:00'
mysql>　SELECT　CONVERT_TZ('2004-01-01　12:00:00','+00:00','+10:00');
->　'2004-01-01　22:00:00'

Note: To use a specified time zone such as ' MET ' or ' Europe/moscow ', first set the correct time zone table.