日期和时间
类型 | 空间(Byte) | 范围 |
---|---|---|
DATETIME | 8 | 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 |
DATE | 3 | 1000-01-01 ~ 9999-12-31 |
TIMESTAMP | 4 | 1970-01-01 00:00:00 ~ 2038-01-01 03:14:07 |
YEAR | 1 | |
TIME | 3 |
对微秒的支持
SELECT CAST("11/01/01 00@01@10.12334" AS DATETIME) AS datetime\G
结果:
*************************** 1. row ***************************
datetime: 2011-01-01 00:01:10
微秒截断,日期为最近的年份
1. 使用MICROSECOND函数提取
示例:
SELECT MICROSECOND("11/01/01 00@01@10.12334") AS datetime\G
结果:
*************************** 1. row ***************************
datetime: 123340
1 row in set (0.01 sec)
版本5.6.4后增加微妙语法
type_name(微妙位数)。type_name可以是 TIME DATATIME TIMESTAMP
示例:
CREATE TABLE temp(a TIME(4));
INSERT INTO temp value("01:12:22.12334");
SELECT * FROM temp \G
DROP TABLE temp;
结果:
*************************** 1. row ***************************
a: 01:12:22.1233
1 row in set (0.00 sec)
TIMESTAMP
存储的是从1970-01-01 00:00:00到现在的毫秒数
与DATETIME不同的地方
1.建表可以设置默认时间
2.更新表示可以设置自动更新为当前时间
示例:
CREATE TABLE temp(
a TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
b TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
INSERT INTO temp(b) value("1994-03-25 22:12:11");
SELECT * FROM temp \G
UPDATE temp SET a="2004-03-25 22:12:11";
SELECT * FROM temp \G
DROP TABLE temp;
结果:
*************************** 1. row ***************************
a: 2016-10-15 01:51:07
b: 1994-03-25 22:12:11
*************************** 1. row ***************************
a: 2004-03-25 22:12:11
b: 2016-10-15 01:51:07
与日期相关的函数
NOW、CURRENT_TIMESTAMP、SYSDATE
获取系统当前时间
示例:
SELECT NOW(),CURRENT_TIMESTAMP(),SYSDATE(),SLEEP(2),NOW(),CURRENT_TIMESTAMP(),SYSDATE()\G
结果:
*************************** 1. row ***************************
NOW(): 2016-10-15 01:58:52
CURRENT_TIMESTAMP(): 2016-10-15 01:58:52
SYSDATE(): 2016-10-15 01:58:52
SLEEP(2): 0
NOW(): 2016-10-15 01:58:52
CURRENT_TIMESTAMP(): 2016-10-15 01:58:52
SYSDATE(): 2016-10-15 01:58:54
NOW和CURRENT_TIMESTAMP同义,获得的是SQL语句执行的时间
SYSDATE是获取当前系统时间
4. DATE_ADD和DATE_SUB
语法:
DATE_ADD(原时间 INTERVAL 时间数字 时间单元)
DATE_SUB(原时间 INTERVAL 时间数字 时间单元)
对闰月的处理:
如果目标年份是闰月,返回2月29,否则返回2月28
那么有些要求返回3月1号就要另行处理
示例:
SELECT DATE_ADD("2004-02-28", INTERVAL 1 DAY);
SELECT DATE_ADD("2004-01-29", INTERVAL 1 MONTH);
SELECT DATE_ADD("2004-01-29", INTERVAL 1 MONTH);
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS tomorrow;
SELECT DATE_ADD(NOW(), INTERVAL -1 DAY) AS yesterday;
DATE_FORMAT
DATE_FORMAT(date,’格式’)
转义符 | 含义 |
---|---|
%Y %y | 4位年,2位年 |
%M %m | 月名 月数字 |
%D %d | 带有英文前缀 日数字 |
%H %h %I | 24小时制 12小时制 |
%i | 分钟 |
%S %s | 秒 |
%W | 星期 |
%w | 周的第几天 |
SELECT DATE_FORMAT(NOW(),'%Y,%m,%d %h.%i.%s')\G
SELECT DATE_FORMAT(NOW(),'%y,%M,%D %H.%i.%S')\G
结果:
DATE_FORMAT(NOW(),'%Y,%m,%d %h.%i.%s'): 2016,10,16 11.30.48
DATE_FORMAT(NOW(),'%y,%M,%D %H.%i.%S'): 16,October,16th 11.35.55
sql问题
生日问题
已知每个用户的出生日期,求到给出日期(2015-10-16)未过的最近的生日。
如果出生在2月29日,那么如果是闰年,返回2月29,如果是平年,返回3月1日
#从employees中截取前十个作为temp.date表
use temp;
CREATE TABLE date LIKE employees.employees;
INSERT INTO date SELECT * FROM employees.employees LIMIT 10;
INSERT INTO date SELECT 10011,'1972-02-29','Liu','Yanzuo','M','1990-2-20';
数据:
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
| 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 |
| 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 |
| 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 |
| 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 |
| 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 |
| 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 |
| 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 |
| 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 |
| 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 |
| 10011 | 1972-02-29 | Liu | Yanzuo | M | 1990-02-20 |
+--------+------------+------------+-----------+--------+------------+
-- 算出当前日期和生日的年份差距
SELECT
CONCAT(first_name,' ',last_name) AS name,
YEAR('2015-10-16')-YEAR(birth_date) AS diff
FROM date;
-- 算出本年和下一年的生日
SELECT
name,
DATE_ADD(birth_date,INTERVAL diff YEAR) AS pre,
DATE_ADD(birth_date,INTERVAL diff+1 YEAR) AS next,
birth_date
FROM (
SELECT
CONCAT(first_name,' ',last_name) AS name,
YEAR('2015-10-16')-YEAR(birth_date) AS diff ,
birth_date
FROM date
) AS a;
-- 对闰年进行处理,DATE_ADD()返回的2月28
SELECT
name,
DATE_ADD(pre,INTERVAL IF(DAY(birth_date)=29 && DAY(pre)=28,1,0) DAY) AS pre,
DATE_ADD(next,INTERVAL IF(DAY(birth_date)=29 && DAY(next)=28,1,0) DAY) AS next
FROM (
SELECT
name,
DATE_ADD(birth_date,INTERVAL diff YEAR) AS pre,
DATE_ADD(birth_date,INTERVAL diff+1 YEAR) AS next,
birth_date
FROM (
SELECT
CONCAT(first_name,' ',last_name) AS name,
YEAR(NOW())-YEAR(birth_date) AS diff ,
birth_date
FROM date
) AS a
) AS b;
-- 比较next和pre与指定时间的大小
SELECT
name,
IF(pre>'2015-10-16',pre,next) AS next_birth_date,
birth_date
FROM (
SELECT
name,
DATE_ADD(pre,INTERVAL IF(DAY(birth_date)=29 && DAY(pre)=28,1,0) DAY) AS pre,
DATE_ADD(next,INTERVAL IF(DAY(birth_date)=29 && DAY(next)=28,1,0) DAY) AS next ,
birth_date
FROM (
SELECT
name,
DATE_ADD(birth_date,INTERVAL diff YEAR) AS pre,
DATE_ADD(birth_date,INTERVAL diff+1 YEAR) AS next,
birth_date
FROM (
SELECT
CONCAT(first_name,' ',last_name) AS name,
YEAR('2015-10-16')-YEAR(birth_date) AS diff ,
birth_date
FROM date
) AS a
) AS b
) AS c;
结果:
+--------------------+-----------------+------------+
| name | next_birth_date | birth_date |
+--------------------+-----------------+------------+
| Georgi Facello | 2016-09-02 | 1953-09-02 |
| Bezalel Simmel | 2016-06-02 | 1964-06-02 |
| Parto Bamford | 2015-12-03 | 1959-12-03 |
| Chirstian Koblick | 2016-05-01 | 1954-05-01 |
| Kyoichi Maliniak | 2016-01-21 | 1955-01-21 |
| Anneke Preusig | 2016-04-20 | 1953-04-20 |
| Tzvetan Zielinski | 2016-05-23 | 1957-05-23 |
| Saniya Kalloufi | 2016-02-19 | 1958-02-19 |
| Sumant Peac | 2016-04-19 | 1952-04-19 |
| Duangkaew Piveteau | 2016-06-01 | 1963-06-01 |
| Liu Yanzuo | 2016-02-29 | 1972-02-29 |
+--------------------+-----------------+------------+
将’2015-10-16’替换为NOW()2016-10-16之后:
+--------------------+-----------------+------------+
| name | next_birth_date | birth_date |
+--------------------+-----------------+------------+
| Georgi Facello | 2017-09-02 | 1953-09-02 |
| Bezalel Simmel | 2017-06-02 | 1964-06-02 |
| Parto Bamford | 2016-12-03 | 1959-12-03 |
| Chirstian Koblick | 2017-05-01 | 1954-05-01 |
| Kyoichi Maliniak | 2017-01-21 | 1955-01-21 |
| Anneke Preusig | 2017-04-20 | 1953-04-20 |
| Tzvetan Zielinski | 2017-05-23 | 1957-05-23 |
| Saniya Kalloufi | 2017-02-19 | 1958-02-19 |
| Sumant Peac | 2017-04-19 | 1952-04-19 |
| Duangkaew Piveteau | 2017-06-01 | 1963-06-01 |
| Liu Yanzuo | 2017-03-01 | 1972-02-29 |
+--------------------+-----------------+------------+
细节问题:
1.DATE_ADD()函数只有两个参数,第二个为表达式,空格隔开
2.子句要用括号括起来,并且要为其命名