电脑技术学习

在MySQL中操作日期和时间

dn001

摘要：日期和时间函数对建立一个站点是非常有用的。站点的主人往往对一个表中的数据何时被更新感兴趣。通过日期和时间函数，你可以在秒级跟踪一个表的改变。

日期和时间函数对建立一个站点是非常有用的。站点的主人往往对一个表中的数据何时被更新感兴趣。通过日期和时间函数，你可以在秒级跟踪一个表的改变。

日期和时间类型是DATETIME、DATE、TIMESTAMP、TIME和YEAR。这些的每一个都有合法值的一个范围，而“零”当你指定确实不合法的值时被使用。注意，MySQL允许你存储某个“不严格地”合法的日期值，例如1999-11-31，原因我们认为它是应用程序的责任来处理日期检查，而不是SQL服务器。为了使日期检查更“快”，MySQL仅检查月份在0-12的范围，天在0-31的范围。上述范围这样被定义是因为MySQL允许你在一个DATE或DATETIME列中存储日期，这里的天或月是零。这对存储你不知道准确的日期的一个生日的应用程序来说是极其有用的，在这种情况下，你简单地存储日期象1999-00-00或1999-01-00。（当然你不能期望从函数如DATE_SUB()或DATE_ADD()得到类似以这些日期的正确值）。

返回当前日期和时间

通过函数GETDATE（），你可以获得当前的日期和时间。例如，

CURDATE() 返回当前日期

CURRENT_DATE

以'YYYY-MM-DD'或YYYYMMDD格式返回今天日期值，取决于函数是在一个字符串还是数字上下文被使用。

mysql> select CURDATE();

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

| CURDATE(); |

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

| 2001-02-20 |

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

mysql> select CURDATE() + 0;

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

| CURDATE()+0 |

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

|;20010220 |

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

CURTIME() 返回当前时间

以'HH:MM:SS'或HHMMSS格式返回当前时间值，取决于函数是在一个字符串还是在数字的上下文被使用。

mysql> select CURTIME();

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

| CURTIME() |

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

| 10:42:38; |

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

mysql> select CURTIME() + 0;

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

| CURTIME()+0 |

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

|;;;104525 |

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

NOW() 返回当前时期和时间

NOW()以YYYY-MM-DD HH:MM:SS的格式或者YYYYMMDDHHMMSS的格式返回日期和时间值，取决于上下文。

mysql>select now();

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

| now();;;;|

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

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

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

mysql>select now()+0;

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

| now()+0;;;;;|

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

| 20010220105635 |

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

这些得到当前日期和时间的函数，对于日期和时间的计算很方便，尤其是计算一个时间到现在的时间差。例如，在pet表中，我们以天为单位计算宠物的年龄：

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 |

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


自动记录数据的改变时间

TIMESTAMP列类型提供一种类型，TIMESTAMP值可以从1970的某时的开始一直到2037年，精度为一秒，其值作为数字显示。你可以使用它自动地用当前的日期和时间标记INSERT或UPDATE的操作。如果你有多个TIMESTAMP列，只有第一个自动更新。

自动更新第一个TIMESTAMP列在下列任何条件下发生：

列没有明确地在一个INSERT或LOAD DATA INFILE语句中指定。

列没有明确地在一个UPDATE语句中指定且一些另外的列改变值。（注意一个UPDATE设置一个列为它已经有的值，这将不引起TIMESTAMP列被更新，因为如果你设置一个列为它当前的值，MySQL为了效率而忽略更改。）

你明确地设定TIMESTAMP列为NULL.

除第一个以外的TIMESTAMP列也可以设置到当前的日期和时间，只要将列设为NULL，或NOW()。

例如，创建如下的表：

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);

查看记录的存储情况：

mysql> SELECT * FROM student;

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

| id| name;| english | chinese | history | time|

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

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

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

你可以看到time列纪录下了数据录入时的时间值。如果你更新改记录，在查看操作的结果：

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


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

| id| name | english | chinese | history | time|

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

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

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

可以清楚的看到，time列的时间被自动更改为修改记录的时间。

有时候你希望不更改任何值，也能打到修改TIMESTAMP列的值，这时只要设置该列的值为NULL，MySQL就可以自动更新TIMESTAMP列的值：

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

mysql> select * from student where id=11;

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

| id| name | english | chinese | history | time|

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

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

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

通过明确地设置希望的值，你可以设置任何TIMESTAMP列为不同于当前日期和时间的值，即使对第一个TIMESTAMP列也是这样。例如，如果，当你创建一个行时，你想要一个TIMESTAMP被设置到当前的日期和时间，但在以后无论何时行被更新时都不改变，你可以使用这样使用：

让MySQL在行被创建时设置列，这将初始化它为当前的日期和时间。

当你执行随后的对该行中其他列的更改时，明确设定TIMESTAMP列为它的当前值。

例如，当你在修改列时，可以把原有的值付给TIMESTAMP列：

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


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

| id| name | english | chinese | history | time|

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

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

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

另一方面，你可能发现，当你想要实现上面这个效果时，很容易用一个你用NOW()初始化的DATETIME列然后不再改变它，这样也许直接些。但是，TIMESTAMP列的以后好处是存储要求比较小，节省空间。TIMESTAMP的存储需求是4字节，而DATETIME列的存储需求是8字节。

返回日期和时间范围

当你分析表中的数据时，你也许希望取出某个特定时间的数据。我们用下面一个表来模仿一个web站点的记录。

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


然后随机的增加几个数据：

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

rand()函数返回一个随机的浮点值，now()函数返回当前时间。多执行上面语句几次，得到一个作为测试的表。

最为测试你还可以增加一个值：

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

这条语句，插入一个entry为”2001-02-08 00:00:00”的值（假定现在为2001年2月8日），你可以查看这个表的值：

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 |

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

你也许对特定的某一天中――比如说2001年2月18日――访问者在你站点上的活动感兴趣。要取出这种类型的数据，你也许会试图使用这样的SELECT语句：

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

不要这样做。这个SELECT语句不会返回正确的记录――它将只返回值为2000-02-08 00:00:00的记录，换句话说，只返回当天零点零时的记录。上面语句的结果为：

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

| data;;| entrydate|

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

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

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

要返回正确的记录，你需要适用日期和时间范围。有不止一种途径可以做到这一点。

1、使用关系运算符和逻辑运算符来限制时间范围

例如，下面的这个SELECT 语句将能返回正确的记录：

mysql> SELECT * FROM weblog

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

这个语句可以完成任务，因为它选取的是表中的日期和时间大于等于2001-02-08 00:00:00并小于2001-02-09 00:00:00的记录。换句话说，它将正确地返回2000年2月8日这一天输入的每一条记录。 其结果为：

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

| 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来返回正确的记录。通过在日期表达式中包含通配符“％”，你可以匹配一个特定日期的所有时间。
这里有一个例子：

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

这个语句可以匹配正确的记录。因为通配符“％”代表了任何时间。

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

| 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、上面两种方法的异同

由于使用关系运算符进行的是比较过程，时转换成内部的存储格式后进行的，因此，因此时间的书写可以不是那么严格要求。

例如，下面几种写法是等价的：

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运算符和模式匹配，是通过比较串值进行的，因此必须使用标准的时间书写格式，YYYY-MM-DD HH-MM-SS。

比较日期和时间

已知两个日期，比较它们的前后，可以直接求出它们的差和零值比较，也可以利用已知的时间函数：

TO_DAYS(date)

给出一个日期date，返回一个天数(从0年的天数)，date可以是一个数字，也可以是一个串值，当然更可以是包含日期的时间类型。

mysql> select TO_DAYS(960501);

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

| TO_DAYS(960501) |

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

|; 729145 |

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

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

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

| TO_DAYS('1997-07-01') |

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

|;;;;;729571 |

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

例如：返回2个时间相差的天数(21世纪已经过去了多少天)

mysql> select to_days(now())-to_days('20010101');

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

| to_days(now()-00000012000000)-to_days('20010101') |

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

|;;;;;38 |

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

上一篇 向MySQL数据表插入行记录（INSERT）

下一篇 MySQL中的字符串模式匹配

相关文章