mysql时间问题_MySQL-mysql教程-PHP中文网

mysql时间问题_MySQL

php中文网

发布： 2016-06-01 13:08:27

原创

1083人浏览过

1. mysql中提供的日期和时间：

小文AI论文

轻松解决论文写作难题，AI论文助您一键完成，仅需一杯咖啡时间，即可轻松问鼎学术高峰！

232

查看详情

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