mysql datetime,timestamp,date
来源：互联网 发布：软件项目介绍ppt 编辑：程序博客网 时间：2024/05/22 06:57
<pre name="code" class="sql"><pre name="code" class="sql">mysql> desc Client -> ;+----------------------------+-------------+------+-----+-------------------+-----------------------------+| Field | Type | Null | Key | Default | Extra |+----------------------------+-------------+------+-----+-------------------+-----------------------------+| sn | int(11) | NO | PRI | NULL | auto_increment || uuid | char(32) | NO | UNI | | || userNick | char(20) | NO | UNI | | || password | char(32) | NO | | | || tradePassword | char(32) | NO | | | || accountType | char(1) | NO | | NULL | || registerType | char(1) | NO | | | || mobilePhone | char(11) | NO | UNI | NULL | || mailBox | char(60) | NO | | | || weixinAccount | char(60) | NO | | | || introducerSn | int(11) | NO | | NULL | || introducer | varchar(50) | NO | | | || status | char(1) | NO | | | || registerTime | datetime | YES | | NULL | || passwordErrNum | tinyint(4) | NO | | 0 | || lastLoginTime | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP || tradePasswordErrNum | tinyint(4) | NO | | 0 | || lastInputTradePasswordTime | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |+----------------------------+-------------+------+-----+-------------------+-----------------------------+18 rows in set (0.00 sec) lastLoginTime | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP mysql> select userNick ,lastLoginTime from Client where lastLoginTime >=timestamp'2015-09-21 00:00:00';select userNick ,lastLoginTime from Client where lastLoginTime >=timestamp'2015-10-10 00:00:00' and lastLoginTime <=timestamp'2015-10-10 15:00:00'select userNick ,lastLoginTime from Client where lastLoginTime >='2015-10-10 00:00:00' and lastLoginTime <='2015-10-10 15:00:00'怎么查等于2015-09-21的呢?mysql> select userNick ,lastLoginTime from Client where date_format(lastLoginTime,'%Y-%m-%d' )='2015-09-21';+-----------------+---------------------+| userNick | lastLoginTime |+-----------------+---------------------+| fangen001 | 2015-09-21 22:24:01 || nanshitx | 2015-09-21 15:53:43 || hgang | 2015-09-21 13:54:33 || mitawang | 2015-09-21 16:00:33 || chengxixin | 2015-09-21 14:37:22 || zhangky | 2015-09-21 10:36:18 || lijunwen | 2015-09-21 16:28:38 || nhcaochong | 2015-09-21 19:45:51 || drs7361 | 2015-09-21 10:30:38 | date time日期类型呢?mysql> desc OfflineRechargeOrder;+-------------+-------------------------+------+-----+--------------+----------------+| Field | Type | Null | Key | Default | Extra |+-------------+-------------------------+------+-----+--------------+----------------+| sn | int(11) | NO | PRI | NULL | auto_increment || clientSn | int(11) | NO | | NULL | || orderNo | char(32) | NO | | | || type | char(1) | NO | | 1 | || amount | decimal(25,10) unsigned | NO | | 0.0000000000 | || bankFlowId | varchar(50) | NO | | | || bankCardID | varchar(20) | YES | | | || evidenceImg | varchar(150) | NO | | | || remark | varchar(500) | NO | | | || status | char(1) | NO | | 1 | || clearTime | date | NO | | NULL | || createdTime | datetime | YES | | NULL | || verifyTime | datetime | YES | | NULL | || verifyUser | int(11) | YES | | NULL | || approveTime | datetime | YES | | NULL | || approveUser | int(11) | YES | | NULL | |+-------------+-------------------------+------+-----+--------------+----------------+16 rows in set (0.00 sec)mysql> select clientSn,createdTime,clearTime from OfflineRechargeOrder where clientSn=96 and date_format(createdTime,'%Y-%m-%d' )='2015-09-21';+----------+---------------------+| clientSn | createdTime |+----------+---------------------+| 96 | 2015-09-21 15:38:52 |+----------+---------------------+1 row in set (0.00 sec)mysql> select clientSn,createdTime,clearTime from OfflineRechargeOrder where clientSn=96 and date_format(createdTime,'%Y-%m-%d' )='2015-09-21';+----------+---------------------+------------+| clientSn | createdTime | clearTime |+----------+---------------------+------------+| 96 | 2015-09-21 15:38:52 | 2015-09-21 |+----------+---------------------+------------+1 row in set (0.00 sec)select clientSn,createdTime,clearTime from OfflineRechargeOrder w where w.createdTime >'2015-10-10 00:00:00' and w.createdTime <'2015-10-10 15:00:00'| clearTime | date date型；mysql> select clientSn,createdTime,clearTime from OfflineRechargeOrder where clearTime='2015-09-21';+----------+---------------------+------------+| clientSn | createdTime | clearTime |+----------+---------------------+------------+| 15 | 2015-09-21 14:48:58 | 2015-09-21 || 615 | 2015-09-21 15:04:31 | 2015-09-21 || 57 | 2015-09-21 15:20:00 | 2015-09-21 || 96 | 2015-09-21 15:38:52 | 2015-09-21 || 461 | 2015-09-21 15:40:28 | 2015-09-21 |+----------+---------------------+------------+5 rows in set (0.00 sec)mysql> create table t100(id int,clearTime date);Query OK, 0 rows affected (0.09 sec)mysql> insert into t100 values(1,'2015-09-21 14:48:58');Query OK, 1 row affected, 1 warning (0.01 sec)mysql> insert into t100 values(2,'2015-09-21 15:04:31');Query OK, 1 row affected, 1 warning (0.01 sec)mysql> insert into t100 valyes(3,'2015-09-21 09:00:00');ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'valyes(3,'2015-09-21 09:00:00')' at line 1mysql> insert into t100 values(3,'2015-09-21 09:00:00');Query OK, 1 row affected, 1 warning (0.02 sec)mysql> select * from t100;+------+------------+| id | clearTime |+------+------------+| 1 | 2015-09-21 || 2 | 2015-09-21 || 3 | 2015-09-21 |+------+------------+3 rows in set (0.00 sec)mysql> select * from t100 a where a.clearTime>'2015-09-21 09:00:00';Empty set (0.00 sec)mysql> create table t200(id int,cleartime datetime); Query OK, 0 rows affected (0.07 sec)mysql> insert into t200 values(3,'2015-09-21 09:00:00');Query OK, 1 row affected (0.01 sec)mysql> select * from t200;+------+---------------------+| id | cleartime |+------+---------------------+| 3 | 2015-09-21 09:00:00 |+------+-------------------
0 0
- mysql datetime,timestamp,date
- mysql Date DateTime TimeStamp
- Mysql------date、datetime、timestamp区别
- DATETIME、DATE和TIMESTAMP类型--MySql数据库
- Mysql date、datetime、timestamp 区别和联系
- MySQL the DATE, DATETIME and TIMESTAMP Types
- mysql的日期类型date, datetime, timestamp
- mysql中datetime、date、timestamp区别
- mysql中date、time、datetime、timestamp区别
- mysql datetime、date、time、timestamp区别
- TIMESTAMP datetime date
- Date、Datetime、Timestamp
- mysql中DATETIME、DATE 和 TIMESTAMP 类型数据使用
- mysql中DATETIME、DATE 和 TIMESTAMP 类型数据使用
- mysql中DATETIME、DATE和TIMESTAMP的区别
- Mysql 时间类型 DATETIME、DATE和TIMESTAMP类型
- Mysql 之Date,DateTime,TimeStamp三种类型的详解
- MySQL数据库中的Date,DateTime和TimeStamp类型
- iOS激情详解之集合视图
- yii 2.0 windows环境搭建
- 面试问题总结之java 深拷贝浅拷贝
- Android自定义SurfaceView与传感器的并用(实现自绘的指北针)
- Mac上配置Gradle
- mysql datetime,timestamp,date
- UVa 12464 - Professor Lazy, Ph.D.
- 用xutils或imageloader加载网络图片实现轮播效果
- hdu 1147 Pick-up sticks（求是否有交点）
- 【ROS学习记录——1】ROS学习资料记录
- 朗国9个月总结与新公司展望
- 关于Java的点点滴滴（1）——Static关键字
- android开发报 intent no such file or directory
- Javascript 学习备忘(一)
