mysql时间问题_MySQL-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

mysql时间问题_MySQL

Jun 01, 2016 pm 01:08 PM

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

mysql&gt; 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&gt; insert into test_time(t_date,t_time,t_dt) values(curdate(),curtime(), now());Query OK, 1 row affected (0.00 sec)mysql&gt; 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)

Copy after login

2. 当修改表中数据时，t_ts自动更新为当前时间:

mysql&gt; update test_time set age=4 where id=4;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql&gt; mysql&gt; 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 |+----+------------+----------+---------------------+---------------------+-----+

Copy after login

3. 时间比较：

mysql&gt; SELECT * FROM test_time WHERE TO_DAYS(NOW()) - TO_DAYS(t_date) <br><pre class="brush:php;toolbar:false">select * from test_time where unix_timestamp(t_ts) &gt;= unix_timestamp() -1800 and unix_timestamp(t_ts) <br><br><p>4. mysql设计friendfeed</p><p>  http://backchannel.org/blog/friendfeed-schemaless-mysql</p><p></p><p><br></p><br><p></p>

Copy after login

Statement of this Website

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn