Operate Date and Time in MySQL (1)

Date and time functions are very useful for creating a site. The website owner is often interested in when data in a table is updated. Using Date and Time Functions, you can track changes to a table in seconds. The DATE and TIME types are DATETIME, DATE, TIMESTAMP, TIME, and YEAR. Each of these has a valid value range, while the value is "zero"

Date and time functions are very useful for creating a site. The website owner is often interested in when data in a table is updated. Using Date and Time Functions, you can track changes to a table in seconds. The DATE and TIME types are DATETIME, DATE, TIMESTAMP, TIME, and YEAR. Each of these has a valid value range, while the value is "zero"

Date and time functions are very useful for creating a site. The website owner is often interested in when data in a table is updated. Using Date and Time Functions, you can track changes to a table in seconds.

The DATE and TIME types are DATETIME, DATE, TIMESTAMP, TIME, and YEAR. Each of these values has a valid value range, and "zero" is used when you specify an invalid value. Note that MySQL allows you to store a valid "loose" date value, such as, because we think it is the responsibility of the application to handle date checks rather than SQL servers. To make the date check "faster", MySQL only checks the range of month in the range of 0-12 and day in the range of 0-31. The preceding range is defined as because MySQL allows you to store dates in a DATE or DATETIME column. The day or month here is zero. This is extremely useful for storing an application that you do not know the exact date of a birthday, in which case you simply store the date like or 1999-01-00. (Of course you cannot expect to get the correct value similar to those dates from functions such as DATE_SUB () or DATE_ADD ).

Returns the current date and time.

By using the GETDATE () function, you can obtain the current date and time. For example,

CURDATE () returns the current date

CURRENT_DATE

Returns today's date value in 'yyyy-MM-DD 'or YYYYMMDD format, depending on whether the function is used in a string or numeric context.

Mysql> select CURDATE ();

+ ------------ +

| CURDATE () |

+ ------------ +

| 2001-02-20 |

+ ------------ +

Mysql> select CURDATE () + 0;

+ ------------- +

| CURDATE () + 0 |

+ ------------- +

| 1, 20010220 |

+ ------------- +

CURTIME () returns the current time

Returns the current time value in 'hh: MM: ss' or HHMMSS format, depending on whether the function is used in a string or in the context of a number.

Mysql> select CURTIME ();

+ ----------- +

| CURTIME () |

+ ----------- +

| 10:42:38 |

+ ----------- +

Mysql> select CURTIME () + 0;

+ ------------- +

| CURTIME () + 0 |

+ ------------- +

| 1, 104525 |

+ ------------- +

NOW () returns the current period and time

NOW () returns the date and time value in the format of YYYY-MM-DD HH: MM: SS or YYYYMMDDHHMMSS, depending on the context.

Mysql> select now ();

+ --------------------- +

| Now () |

+ --------------------- +

| 10:45:57 |

+ --------------------- +

Mysql> select now () + 0;

+ ---------------- +

| Now () + 0 |

+ ---------------- +

| 1, 20010220105635 |

+ ---------------- +

These functions obtain the current date and time, which is very convenient for date and time calculation, especially for calculating the time difference between a time and the present. For example, in the pet table, we calculate the age of pets in days:

Mysql> SELECT name, CURDATE ()-birth FROM pet;

+ ---------- + ----------------- +

| Name | CURDATE ()-birth |

+ ---------- + ----------------- +

| Fluffy | 1, 80016 |

| Clags | 69903 |

| Buffy | 119707 |

| Chirpy | 29309 |

| Fang | 1, 109393 |

| Bow.| 109389 |

| Whistler | 39011 |

| Slim | 49791 |

| Puffball | 19890 |

+ ---------- + ----------------- +