Mysql日期类型

最新推荐文章于 2023-09-14 21:18:49 发布

刘彦佐

最新推荐文章于 2023-09-14 21:18:49 发布

阅读量522

点赞数

分类专栏： MySQL 文章标签： mysql日期类型

本文链接：https://blog.csdn.net/u012562931/article/details/53589431

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

日期和时间

类型	空间(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.子句要用括号括起来，并且要为其命名

刘彦佐

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Mysql日期类型

SQL 日期类型的一些笔记
复制链接

扫一扫

专栏目录