1. mysql中提供的日期和时间：
mysql> desc test_time;+--------+---------------------+------+-----+-------------------+-----------------------------+| Field | Type | Null | Key | Default | Extra |+--------+---------------------+------+-----+-------------------+-----------------------------+| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment || t_date | date | YES | | NULL | || t_time | time | YES | | NULL | || t_ts | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP || t_dt | datetime | YES | | NULL | |+--------+---------------------+------+-----+-------------------+-----------------------------+5 rows in set (0.00 sec)mysql> insert into test_time(t_date,t_time,t_dt) values(curdate(),curtime(), now());Query OK, 1 row affected (0.00 sec)mysql> select * from test_time;+----+------------+----------+---------------------+---------------------+| id | t_date | t_time | t_ts | t_dt |+----+------------+----------+---------------------+---------------------+| 1 | 2014-06-19 | 15:53:39 | 2014-06-19 15:53:39 | 2014-06-19 15:53:39 || 2 | 2014-06-19 | 15:53:57 | 2014-06-19 15:53:57 | 2014-06-19 15:53:57 || 3 | 2014-06-19 | 15:54:01 | 2014-06-19 15:54:01 | 2014-06-19 15:54:01 || 4 | 2014-06-19 | 15:54:38 | 2014-06-19 15:54:38 | 2014-06-19 15:54:38 |+----+------------+----------+---------------------+---------------------+4 rows in set (0.00 sec)
2. 当修改表中数据时，t_ts自动更新为当前时间:
mysql> update test_time set age=4 where id=4;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> mysql> select * from test_time;+----+------------+----------+---------------------+---------------------+-----+| id | t_date | t_time | t_ts | t_dt | age |+----+------------+----------+---------------------+---------------------+-----+| 1 | 2014-06-19 | 15:53:39 | 2014-06-19 15:59:32 | 2014-06-19 15:53:39 | 1 || 2 | 2014-06-19 | 15:53:57 | 2014-06-19 15:59:41 | 2014-06-19 15:53:57 | 2 || 3 | 2014-06-19 | 15:54:01 | 2014-06-19 15:59:49 | 2014-06-19 15:54:01 | 3 || 4 | 2014-06-19 | 15:54:38 | 2014-06-19 15:59:55 | 2014-06-19 15:54:38 | 4 |+----+------------+----------+---------------------+---------------------+-----+
3. 时间比较：
mysql> SELECT * FROM test_time WHERE TO_DAYS(NOW()) - TO_DAYS(t_date) <= 30;+----+------------+----------+---------------------+---------------------+-----+| id | t_date | t_time | t_ts | t_dt | age |+----+------------+----------+---------------------+---------------------+-----+| 1 | 2014-06-19 | 15:53:39 | 2014-06-19 15:59:32 | 2014-06-19 15:53:39 | 1 || 2 | 2014-06-19 | 15:53:57 | 2014-06-19 15:59:41 | 2014-06-19 15:53:57 | 2 || 3 | 2014-06-19 | 15:54:01 | 2014-06-19 15:59:49 | 2014-06-19 15:54:01本文来源gaodai.ma#com搞##代!^码@网3| 3 || 4 | 2014-06-19 | 15:54:38 | 2014-06-19 15:59:55 | 2014-06-19 15:54:38 | 4 |+----+------------+----------+---------------------+---------------------+-----+4 rows in set (0.00 sec)
select * from test_time where unix_timestamp(t_ts) >= unix_timestamp() -1800 and unix_timestamp(t_ts) <= unix_timestamp();
4. mysql设计friendfeed
http://backchannel.org/blog/friendfeed-schemaless-mysql
搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：mysql时间问题_MySQL
转载请注明原文链接：mysql时间问题_MySQL
