在MySQL中操作日期和時(shí)間

2024-07-24 12:58:22

字體：大中小

供稿：網(wǎng)友

摘要：日期和時(shí)間函數(shù)對(duì)建立一個(gè)站點(diǎn)是非常有用的。站點(diǎn)的主人往往對(duì)一個(gè)表中的數(shù)據(jù)何時(shí)被更新感興趣。通過(guò)日期和時(shí)間函數(shù)，你可以在秒級(jí)跟蹤一個(gè)表的改變。

日期和時(shí)間函數(shù)對(duì)建立一個(gè)站點(diǎn)是非常有用的。站點(diǎn)的主人往往對(duì)一個(gè)表中的數(shù)據(jù)何時(shí)被更新感興趣。通過(guò)日期和時(shí)間函數(shù)，你可以在秒級(jí)跟蹤一個(gè)表的改變。

日期和時(shí)間類型是DATETIME、DATE、TIMESTAMP、TIME和YEAR。這些的每一個(gè)都有合法值的一個(gè)范圍，而“零”當(dāng)你指定確實(shí)不合法的值時(shí)被使用。注意，MySQL允許你存儲(chǔ)某個(gè)“不嚴(yán)格地”合法的日期值，例如1999-11-31，原因我們認(rèn)為它是應(yīng)用程序的責(zé)任來(lái)處理日期檢查，而不是SQL服務(wù)器。為了使日期檢查更“快”，MySQL僅檢查月份在0-12的范圍，天在0-31的范圍。上述范圍這樣被定義是因?yàn)镸ySQL允許你在一個(gè)DATE或DATETIME列中存儲(chǔ)日期，這里的天或月是零。這對(duì)存儲(chǔ)你不知道準(zhǔn)確的日期的一個(gè)生日的應(yīng)用程序來(lái)說(shuō)是極其有用的，在這種情況下，你簡(jiǎn)單地存儲(chǔ)日期象1999-00-00或1999-01-00。（當(dāng)然你不能期望從函數(shù)如DATE_SUB()或DATE_ADD()得到類似以這些日期的正確值）。

返回當(dāng)前日期和時(shí)間

通過(guò)函數(shù)GETDATE（），你可以獲得當(dāng)前的日期和時(shí)間。例如，

CURDATE() 返回當(dāng)前日期

CURRENT_DATE

以'YYYY-MM-DD'或YYYYMMDD格式返回今天日期值，取決于函數(shù)是在一個(gè)字符串還是數(shù)字上下文被使用。

mysql> select CURDATE();

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

| CURDATE() |

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

| 2001-02-20 |

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

mysql> select CURDATE() + 0;

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

| CURDATE()+0 |

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

| 20010220 |

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

CURTIME() 返回當(dāng)前時(shí)間

以'HH:MM:SS'或HHMMSS格式返回當(dāng)前時(shí)間值，取決于函數(shù)是在一個(gè)字符串還是在數(shù)字的上下文被使用。

mysql> select CURTIME();

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

| CURTIME() |

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

| 10:42:38 |

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

mysql> select CURTIME() + 0;

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

| CURTIME()+0 |

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

| 104525 |

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

NOW() 返回當(dāng)前時(shí)期和時(shí)間

NOW()以YYYY-MM-DD HH:MM:SS的格式或者YYYYMMDDHHMMSS的格式返回日期和時(shí)間值，取決于上下文。

mysql>select now();

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

| now() |

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

| 2001-02-20 10:45:57 |

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

mysql>select now()+0;

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

| now()+0 |

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

| 20010220105635 |

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

這些得到當(dāng)前日期和時(shí)間的函數(shù)，對(duì)于日期和時(shí)間的計(jì)算很方便，尤其是計(jì)算一個(gè)時(shí)間到現(xiàn)在的時(shí)間差。例如，在pet表中，我們以天為單位計(jì)算寵物的年齡：

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

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

| name | CURDATE()-birth |

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

| Fluffy | 80016 |

| Claws | 69903 |

| Buffy | 119707 |

| Chirpy | 29309 |

| Fang | 109393 |

| Bowser | 109389 |

| Whistler | 39011 |

| Slim | 49791 |

| Puffball | 19890 |

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

自動(dòng)記錄數(shù)據(jù)的改變時(shí)間

TIMESTAMP列類型提供一種類型，TIMESTAMP值可以從1970的某時(shí)的開始一直到2037年，精度為一秒，其值作為數(shù)字顯示。你可以使用它自動(dòng)地用當(dāng)前的日期和時(shí)間標(biāo)記INSERT或UPDATE的操作。如果你有多個(gè)TIMESTAMP列，只有第一個(gè)自動(dòng)更新。

自動(dòng)更新第一個(gè)TIMESTAMP列在下列任何條件下發(fā)生：

列沒(méi)有明確地在一個(gè)INSERT或LOAD DATA INFILE語(yǔ)句中指定。

列沒(méi)有明確地在一個(gè)UPDATE語(yǔ)句中指定且一些另外的列改變值。（注意一個(gè)UPDATE設(shè)置一個(gè)列為它已經(jīng)有的值，這將不引起TIMESTAMP列被更新，因?yàn)槿绻阍O(shè)置一個(gè)列為它當(dāng)前的值，MySQL為了效率而忽略更改。）

你明確地設(shè)定TIMESTAMP列為NULL.

除第一個(gè)以外的TIMESTAMP列也可以設(shè)置到當(dāng)前的日期和時(shí)間，只要將列設(shè)為NULL，或NOW()。

例如，創(chuàng)建如下的表：

mysql> CREATE TABLE student
-> (
-> id int,
-> name char(16),
-> english tinyint,
-> chinese tinyint,
-> history tinyint,
-> time timestamp
-> );

向表中插入記錄，可以查看效果：

mysql> INSERT student(id,name,englisht,Chinese,history) VALUES(11,”Tom”,66,93,67);

查看記錄的存儲(chǔ)情況：

mysql> SELECT * FROM student;

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

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

| 11 | Tom | 66 | 93 | 67 | 20010220123335 |

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

你可以看到time列紀(jì)錄下了數(shù)據(jù)錄入時(shí)的時(shí)間值。如果你更新改記錄，在查看操作的結(jié)果：

mysql> UPDATE student SET english=76 WHERE id=11;
mysql> SELECT * FROM student;

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

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

| 11 | Tom | 76 | 93 | 67 | 20010220125736 |

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

可以清楚的看到，time列的時(shí)間被自動(dòng)更改為修改記錄的時(shí)間。

有時(shí)候你希望不更改任何值，也能打到修改TIMESTAMP列的值，這時(shí)只要設(shè)置該列的值為NULL，MySQL就可以自動(dòng)更新TIMESTAMP列的值：

mysql> UPDATE student SET time=NULL WHERE id=11;

mysql> select * from student where id=11;

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

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

| 11 | Tom | 76 | 93 | 67 | 20010220130517 |

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

通過(guò)明確地設(shè)置希望的值，你可以設(shè)置任何TIMESTAMP列為不同于當(dāng)前日期和時(shí)間的值，即使對(duì)第一個(gè)TIMESTAMP列也是這樣。例如，如果，當(dāng)你創(chuàng)建一個(gè)行時(shí)，你想要一個(gè)TIMESTAMP被設(shè)置到當(dāng)前的日期和時(shí)間，但在以后無(wú)論何時(shí)行被更新時(shí)都不改變，你可以使用這樣使用：

讓MySQL在行被創(chuàng)建時(shí)設(shè)置列，這將初始化它為當(dāng)前的日期和時(shí)間。

當(dāng)你執(zhí)行隨后的對(duì)該行中其他列的更改時(shí)，明確設(shè)定TIMESTAMP列為它的當(dāng)前值。

例如，當(dāng)你在修改列時(shí)，可以把原有的值付給TIMESTAMP列：

mysql> UPDATE student SET english=66,time=time WHERE id=11;
mysql> select * from student where id=11;

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

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

| 11 | Tom | 66 | 93 | 67 | 20010220130517 |

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

另一方面，你可能發(fā)現(xiàn)，當(dāng)你想要實(shí)現(xiàn)上面這個(gè)效果時(shí)，很容易用一個(gè)你用NOW()初始化的DATETIME列然后不再改變它，這樣也許直接些。但是，TIMESTAMP列的以后好處是存儲(chǔ)要求比較小，節(jié)省空間。TIMESTAMP的存儲(chǔ)需求是4字節(jié)，而DATETIME列的存儲(chǔ)需求是8字節(jié)。

返回日期和時(shí)間范圍

當(dāng)你分析表中的數(shù)據(jù)時(shí)，你也許希望取出某個(gè)特定時(shí)間的數(shù)據(jù)。我們用下面一個(gè)表來(lái)模仿一個(gè)web站點(diǎn)的記錄。

mysql> CREATE TABLE weblog
-> (
-> data float,
-> entrydate datetime
-> );

然后隨機(jī)的增加幾個(gè)數(shù)據(jù)：

mysql> INSERT weblog VALUES(rand(),now());

rand()函數(shù)返回一個(gè)隨機(jī)的浮點(diǎn)值，now()函數(shù)返回當(dāng)前時(shí)間。多執(zhí)行上面語(yǔ)句幾次，得到一個(gè)作為測(cè)試的表。

最為測(cè)試你還可以增加一個(gè)值：

mysql> INSERT weblog VALUES(rand(),”2001-02-08”);

這條語(yǔ)句，插入一個(gè)entry為”2001-02-08 00:00:00”的值（假定現(xiàn)在為2001年2月8日），你可以查看這個(gè)表的值：

mysql> select * from weblog;

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

| data | entrydate |

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

| 0.973723 | 2001-02-08 00:00:00 |

| 0.437768 | 2001-02-08 13:57:06 |

| 0.327279 | 2001-02-08 13:57:09 |

| 0.0931809 | 2001-02-08 13:58:29 |

| 0.198805 | 2001-02-08 13:57:54 |

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

你也許對(duì)特定的某一天中――比如說(shuō)2001年2月18日――訪問(wèn)者在你站點(diǎn)上的活動(dòng)感興趣。要取出這種類型的數(shù)據(jù)，你也許會(huì)試圖使用這樣的SELECT語(yǔ)句：

mysql> SELECT * FROM weblog WHERE entrydate="2001-02-08"

不要這樣做。這個(gè)SELECT語(yǔ)句不會(huì)返回正確的記錄――它將只返回值為2000-02-08 00:00:00的記錄，換句話說(shuō)，只返回當(dāng)天零點(diǎn)零時(shí)的記錄。上面語(yǔ)句的結(jié)果為：

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

| data | entrydate |

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

| 0.973723 | 2001-02-08 00:00:00 |

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

要返回正確的記錄，你需要適用日期和時(shí)間范圍。有不止一種途徑可以做到這一點(diǎn)。

1、使用關(guān)系運(yùn)算符和邏輯運(yùn)算符來(lái)限制時(shí)間范圍

例如，下面的這個(gè)SELECT 語(yǔ)句將能返回正確的記錄：

mysql> SELECT * FROM weblog

-> WHERE　entrydate>="2001-02-08" AND entrydate<"2001-02-09" ;

這個(gè)語(yǔ)句可以完成任務(wù)，因?yàn)樗x取的是表中的日期和時(shí)間大于等于2001-02-08 00:00:00并小于2001-02-09 00:00:00的記錄。換句話說(shuō)，它將正確地返回2000年2月8日這一天輸入的每一條記錄。其結(jié)果為：

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

| data | entrydate |

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

| 0.973723 | 2001-02-08 00:00:00 |

| 0.437768 | 2001-02-08 13:57:06 |

| 0.327279 | 2001-02-08 13:57:09 |

| 0.0931809 | 2001-02-08 13:58:29 |

| 0.198805 | 2001-02-08 13:57:54 |

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

2、另一種方法是，你可以使用LIKE來(lái)返回正確的記錄。通過(guò)在日期表達(dá)式中包含通配符“％”，你可以匹配一個(gè)特定日期的所有時(shí)間。
這里有一個(gè)例子：

mysql> SELECT * FROM weblog WHERE entrydate LIKE '2001-02-08%' ;

這個(gè)語(yǔ)句可以匹配正確的記錄。因?yàn)橥ㄅ浞埃ァ贝砹巳魏螘r(shí)間。

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

| data | entrydate |

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

| 0.973723 | 2001-02-08 00:00:00 |

| 0.437768 | 2001-02-08 13:57:06 |

| 0.327279 | 2001-02-08 13:57:09 |

| 0.0931809 | 2001-02-08 13:58:29 |

| 0.198805 | 2001-02-08 13:57:54 |

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

3、上面兩種方法的異同

由于使用關(guān)系運(yùn)算符進(jìn)行的是比較過(guò)程，時(shí)轉(zhuǎn)換成內(nèi)部的存儲(chǔ)格式后進(jìn)行的，因此，因此時(shí)間的書寫可以不是那么嚴(yán)格要求。

例如，下面幾種寫法是等價(jià)的：

mysql> SELECT * FROM weblog WHERE entrydate>="2001-02-08";
mysql> SELECT * FROM weblog WHERE entrydate>="2001-2-8";
mysql> SELECT * FROM weblog WHERE entrydate>="2001*02*08";
mysql> SELECT * FROM weblog WHERE entrydate>="20010208";
SELECT * FROM weblog WHERE entrydate>="2001/2/8";

而使用LIKE運(yùn)算符和模式匹配，是通過(guò)比較串值進(jìn)行的，因此必須使用標(biāo)準(zhǔn)的時(shí)間書寫格式，YYYY-MM-DD HH-MM-SS。

比較日期和時(shí)間

已知兩個(gè)日期，比較它們的前后，可以直接求出它們的差和零值比較，也可以利用已知的時(shí)間函數(shù)：

TO_DAYS(date)

給出一個(gè)日期date，返回一個(gè)天數(shù)(從0年的天數(shù))，date可以是一個(gè)數(shù)字，也可以是一個(gè)串值，當(dāng)然更可以是包含日期的時(shí)間類型。

mysql> select TO_DAYS(960501);

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

| TO_DAYS(960501) |

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

| 729145 |

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

mysql> select TO_DAYS('1997-07-01');