MySQL Study之--MySQL日期函数

向右看齐 2022-08-03 00:51 159阅读 0赞

**MySQL Study之--MySQL日期函数**

**一、案例分析：**

**Oracle 脚本：**

\[sql\] [view plain][][copy][view plain][print][view plain][?][view plain]

1.  CONNECT SCOTT/tiger  
2.  CREATE TABLE DEPT  
3.         (DEPTNO NUMBER(2) CONSTRAINT PK\_DEPT PRIMARY KEY,  
4.          DNAME VARCHAR2(14) ,  
5.          LOC VARCHAR2(13) ) ;  
6.    
7.  CREATE TABLE EMP  
8.         (EMPNO NUMBER(4) CONSTRAINT PK\_EMP PRIMARY KEY,  
9.          ENAME VARCHAR2(10),  
10.         JOB VARCHAR2(9),  
11.         MGR NUMBER(4),  
12.         HIREDATE DATE,  
13.         SAL NUMBER(7,2),  
14.         COMM NUMBER(7,2),  
15.         DEPTNO NUMBER(2) CONSTRAINT FK\_DEPTNO REFERENCES DEPT);  
16. INSERT INTO DEPT VALUES  
17.         (10,'ACCOUNTING','NEW YORK');  
18. INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');  
19. INSERT INTO DEPT VALUES  
20.         (30,'SALES','CHICAGO');  
21. INSERT INTO DEPT VALUES  
22.         (40,'OPERATIONS','BOSTON');  
23. INSERT INTO EMP VALUES  
24. (7369,'SMITH','CLERK',7902,to\_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);  
25. INSERT INTO EMP VALUES  
26. (7499,'ALLEN','SALESMAN',7698,to\_date('20-2-1981','dd-mm-yyyy'),1600,300,30);  
27. INSERT INTO EMP VALUES  
28. (7521,'WARD','SALESMAN',7698,to\_date('22-2-1981','dd-mm-yyyy'),1250,500,30);  
29. INSERT INTO EMP VALUES  
30. (7566,'JONES','MANAGER',7839,to\_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);  
31. INSERT INTO EMP VALUES  
32. (7654,'MARTIN','SALESMAN',7698,to\_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);  
33. INSERT INTO EMP VALUES  
34. (7698,'BLAKE','MANAGER',7839,to\_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);  
35. INSERT INTO EMP VALUES  
36. (7782,'CLARK','MANAGER',7839,to\_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);  
37. INSERT INTO EMP VALUES  
38. (7788,'SCOTT','ANALYST',7566,to\_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);  
39. INSERT INTO EMP VALUES  
40. (7839,'KING','PRESIDENT',NULL,to\_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);  
41. INSERT INTO EMP VALUES  
42. (7844,'TURNER','SALESMAN',7698,to\_date('8-9-1981','dd-mm-yyyy'),1500,0,30);  
43. INSERT INTO EMP VALUES  
44. (7876,'ADAMS','CLERK',7788,to\_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);  
45. INSERT INTO EMP VALUES  
46. (7900,'JAMES','CLERK',7698,to\_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);  
47. INSERT INTO EMP VALUES  
48. (7902,'FORD','ANALYST',7566,to\_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);  
49. INSERT INTO EMP VALUES  
50. (7934,'MILLER','CLERK',7782,to\_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);  
51.   
52. CREATE TABLE BONUS  
53.         (  
54.         ENAME VARCHAR2(10)      ,  
55.         JOB VARCHAR2(9)  ,  
56.         SAL NUMBER,  
57.         COMM NUMBER  
58.         ) ;  
59. CREATE TABLE SALGRADE  
60.       ( GRADE NUMBER,  
61.         LOSAL NUMBER,  
62.         HISAL NUMBER );  
63.   
64. INSERT INTO SALGRADE VALUES (1,700,1200);  
65. INSERT INTO SALGRADE VALUES (2,1201,1400);  
66. INSERT INTO SALGRADE VALUES (3,1401,2000);  
67. INSERT INTO SALGRADE VALUES (4,2001,3000);  
68. INSERT INTO SALGRADE VALUES (5,3001,9999);  
69. COMMIT;  
70. EXIT

**MySQL 脚本：**

\[sql\] [view plain][][copy][view plain][print][view plain][?][view plain]

1.  use prod;  
2.  CREATE TABLE dept  
3.         (deptNO int(2),   
4.          DNAME VARCHAR(14) ,  
5.          LOC VARCHAR(13),  
6.          primary key(deptNO) ) ;  
7.    
8.  CREATE TABLE emp  
9.         (empNO int(4) ,  
10.         ENAME VARCHAR(10),  
11.         JOB VARCHAR(9),  
12.         MGR int(4),  
13.         HIREDATE DATE,  
14.         SAL int(7),  
15.         COMM int(7),  
16.         deptNO int(2),  
17.         primary key(empno),   
18.         foreign key(deptno) REFERENCES dept(deptno));  
19.   
20. INSERT INTO dept VALUES  
21.         (10,'ACCOUNTING','NEW YORK');  
22. INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');  
23. INSERT INTO dept VALUES  
24.         (30,'SALES','CHICAGO');  
25. INSERT INTO dept VALUES  
26.         (40,'OPERATIONS','BOSTON');  
27. INSERT INTO emp VALUES  
28. (7369,'SMITH','CLERK',7902,date\_format('1980-12-17','%Y-%m-%d'),800,NULL,20);  
29. INSERT INTO emp VALUES  
30. (7499,'ALLEN','SALESMAN',7698,date\_format('1981-2-20','%Y-%m-%d'),1600,300,30);  
31. INSERT INTO emp VALUES  
32. (7521,'WARD','SALESMAN',7698,date\_format('1981-2-22','%Y-%m-%d'),1250,500,30);  
33. INSERT INTO emp VALUES  
34. (7566,'JONES','MANAGER',7839,date\_format('1981-4-2','%Y-%m-%d'),2975,NULL,20);  
35. INSERT INTO emp VALUES  
36. (7654,'MARTIN','SALESMAN',7698,date\_format('1981-9-28','%Y-%m-%d'),1250,1400,30);  
37. INSERT INTO emp VALUES  
38. (7698,'BLAKE','MANAGER',7839,date\_format('1981-5-1','%Y-%m-%d'),2850,NULL,30);  
39. INSERT INTO emp VALUES  
40. (7782,'CLARK','MANAGER',7839,date\_format('1981-6-9','%Y-%m-%d'),2450,NULL,10);  
41. INSERT INTO emp VALUES  
42. (7788,'SCOTT','ANALYST',7566,date\_format('1987-7-13','%Y-%m-%d'),3000,NULL,20);  
43. INSERT INTO emp VALUES  
44. (7839,'KING','PRESIDENT',NULL,date\_format('1981-11-17','%Y-%m-%d'),5000,NULL,10);  
45. INSERT INTO emp VALUES  
46. (7844,'TURNER','SALESMAN',7698,date\_format('1981-9-8','%Y-%m-%d'),1500,0,30);  
47. INSERT INTO emp VALUES  
48. (7876,'ADAMS','CLERK',7788,date\_format('1987-6-13', '%Y-%m-%d'),1100,NULL,20);  
49. INSERT INTO emp VALUES  
50. (7900,'JAMES','CLERK',7698,date\_format('1981-12-3','%Y-%m-%d'),950,NULL,30);  
51. INSERT INTO emp VALUES  
52. (7902,'FORD','ANALYST',7566,date\_format('1981-12-3','%Y-%m-%d'),3000,NULL,20);  
53. INSERT INTO emp VALUES  
54. (7934,'MILLER','CLERK',7782,date\_format('1982-1-23','%Y-%m-%d'),1300,NULL,10);  
55. CREATE TABLE BONUS  
56.         (  
57.         ENAME VARCHAR(10)      ,  
58.         JOB VARCHAR(9)  ,  
59.         SAL int,  
60.         COMM int  
61.         ) ;  
62.   
63. CREATE TABLE SALGRADE  
64.       ( GRADE int,  
65.         LOSAL int,  
66.         HISAL int );  
67.   
68. INSERT INTO SALGRADE VALUES (1,700,1200);  
69. INSERT INTO SALGRADE VALUES (2,1201,1400);  
70. INSERT INTO SALGRADE VALUES (3,1401,2000);  
71. INSERT INTO SALGRADE VALUES (4,2001,3000);  
72. INSERT INTO SALGRADE VALUES (5,3001,9999);  
73. COMMIT;

**二、MySQL 日期函数**

**DATE\_FORMAT(date,format)**  
根据format字符串格式化date值。下列修饰符可以被用在format字符串中：

%M 月名字(January……December)
    %W 星期名字(Sunday……Saturday)
    %D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）
    %Y 年, 数字, 4 位
    %y 年, 数字, 2 位
    %a 缩写的星期名字(Sun……Sat)
    %d 月份中的天数, 数字(00……31)
    %e 月份中的天数, 数字(0……31)
    %m 月, 数字(01……12)
    %c 月, 数字(1……12)
    %b 缩写的月份名字(Jan……Dec)
    %j 一年中的天数(001……366)
    %H 小时(00……23)
    %k 小时(0……23)
    %h 小时(01……12)
    %I 小时(01……12)
    %l 小时(1……12)
    %i 分钟, 数字(00……59)
    %r 时间,12 小时(hh:mm:ss [AP]M)
    %T 时间,24 小时(hh:mm:ss)
    %S 秒(00……59)
    %s 秒(00……59)
    %p AM或PM
    %w 一个星期中的天数(0=Sunday ……6=Saturday ）
    %U 星期(0……52), 这里星期天是星期的第一天
    %u 星期(0……52), 这里星期一是星期的第一天
    %% 一个文字“%”。

**案例：**

mysql> select date_format( now(),'%Y-%M-%d %H:%i:%s');
    +-----------------------------------------+
    | date_format( now(),'%Y-%M-%d %H:%i:%s') |
    +-----------------------------------------+
    | 2015-July-29 12:01:02                   |
    +-----------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select now();
    +---------------------+
    | now()               |
    +---------------------+
    | 2015-07-29 12:01:12 |
    +---------------------+
    1 row in set (0.01 sec)
    
    mysql> select date_format( sysdate(),'%Y-%M-%d %H:%i:%s');
    +---------------------------------------------+
    | date_format( sysdate(),'%Y-%M-%d %H:%i:%s') |
    +---------------------------------------------+
    | 2015-July-29 12:01:20                       |
    +---------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select sysdate();
    +---------------------+
    | sysdate()           |
    +---------------------+
    | 2015-07-29 12:01:31 |
    +---------------------+
    1 row in set (0.00 sec)

所有的其他字符不做解释被复制到结果中。

mysql> selectDATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
    -> 'Saturday October 1997'
    mysql> select DATE_FORMAT('1997-10-04 22:23:00','%H:%i:%s');
    -> '22:23:00'
    mysql> select DATE_FORMAT('1997-10-04 22:23:00',
    '%D %y %a %d %m %b %j');
    -> '4th 97 Sat 04 10 Oct 277'
    mysql> select DATE_FORMAT('1997-10-04 22:23:00',
    '%H %k %I %r %T %S %w');
    -> '22 22 10 10:23:00 PM 22:23:00 00 6'

MySQL3.23中，在格式修饰符字符前需要%。在MySQL更早的版本中，%是可选的。  
  
**TIME\_FORMAT(time,format)**  
这象上面的DATE\_FORMAT()函数一样使用，但是format字符串只能包含处理小时、分钟和秒的那些格式修饰符。  
  
其他修饰符产生一个NULL值或0。  
**CURDATE():  
CURRENT\_DATE**  
以'YYYY-MM-DD'或YYYYMMDD格式返回今天日期值，取决于函数是在一个字符串还是数字上下文被使用。

mysql> select CURDATE();
    -> '1997-12-15'
    mysql> select CURDATE() + 0;
    -> 19971215

**CURTIME():  
CURRENT\_TIME**  
以'HH:MM:SS'或HHMMSS格式返回当前时间值，取决于函数是在一个字符串还是在数字的上下文被使用。

mysql> select CURTIME();
    -> '23:50:26'
    mysql> select CURTIME() + 0;
    -> 235026

**案例：**

mysql> select time_format(now(),'%H:%i:%s');
    +-------------------------------+
    | time_format(now(),'%H:%i:%s') |
    +-------------------------------+
    | 12:04:43                      |
    +-------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select curdate();
    +------------+
    | curdate()  |
    +------------+
    | 2015-07-29 |
    +------------+
    1 row in set (0.00 sec)
    
    mysql> select curtime();
    +-----------+
    | curtime() |
    +-----------+
    | 12:05:20  |
    +-----------+
    1 row in set (0.00 sec)
    
    mysql> select curtime() "time";
    +----------+
    | time     |
    +----------+
    | 12:05:33 |
    +----------+
    1 row in set (0.00 sec)

**NOW():  
SYSDATE():  
CURRENT\_TIMESTAMP**  
以'YYYY-MM-DDHH:MM:SS'或YYYYMMDDHHMMSS格式返回当前的日期和时间，取决于函数是在一个字符串还是在数字的  
上下文被使用。

mysql> select NOW();
    -> '1997-12-15 23:50:26'
    mysql> select NOW() + 0;
    -> 19971215235026

**UNIX\_TIMESTAMP()  
UNIX\_TIMESTAMP(date)**  
如果没有参数调用，返回一个Unix时间戳记(从'1970-01-0100:00:00'GMT开始的秒数)。如果UNIX\_TIMESTAMP()用一个date参数被调用，它返回从'1970-01-0100:00:00'GMT开始的秒数值。date可以是一个DATE字符串、一个DATETIME字符串、一个TIMESTAMP或以YYMMDD或YYYYMMDD格式的本地时间的一个数字。

mysql> select UNIX_TIMESTAMP();
    -> 882226357
    mysql> select UNIX_TIMESTAMP('1997-10-04 22:23:00');
    -> 875996580

当UNIX\_TIMESTAMP被用于一个TIMESTAMP列，函数将直接接受值，没有隐含的“string-to-unix-timestamp”变换www.knowsky.com。  
**FROM\_UNIXTIME(unix\_timestamp):**  
以'YYYY-MM-DDHH:MM:SS'或YYYYMMDDHHMMSS格式返回unix\_timestamp参数所表示的值，取决于函数是在一个字符串还是或数字上下文中被使用。

mysql> select FROM_UNIXTIME(875996580);
    -> '1997-10-04 22:23:00'
    mysql> select FROM_UNIXTIME(875996580) + 0;
    -> 19971004222300

**FROM\_UNIXTIME(unix\_timestamp,format):**  
返回表示 Unix时间标记的一个字符串，根据format字符串格式化。format可以包含与DATE\_FORMAT()函数列出的条目同样的修饰符。

mysql> select FROM_UNIXTIME(UNIX_TIMESTAMP(),
    '%Y %D %M %h:%i:%s %x');
    -> '1997 23rd December 03:43:30 x'

**SEC\_TO\_TIME(seconds):**  
返回seconds参数，变换成小时、分钟和秒，值以'HH:MM:SS'或HHMMSS格式化，取决于函数是在一个字符串还是在数字上下文中被使用。

mysql> select SEC_TO_TIME(2378);
    -> '00:39:38'
    mysql> select SEC_TO_TIME(2378) + 0;
    -> 3938

**TIME\_TO\_SEC(time):**  
返回time参数，转换成秒。

mysql> select TIME_TO_SEC('22:23:00');
    -> 80580
    mysql> select TIME_TO_SEC('00:39:38');
    -> 2378

[view plain]: http://blog.csdn.net/lqx0405/article/details/47123217#