LightDB23.1新特性MySQL模式支持MySQL str_to_date date_format函数

str_to_date 和 date_format 函数是 MySQL 中用于处理日期和时间的两个常用函数。str_to_date 函数用于将字符串转换为日期或日期时间格式，而 date_format 函数用于将日期或日期时间格式转换为指定的字符串格式。下面是这两个函数的使用方法：

创建测试表

CREATE TABLE EMP
(   EMPNO    bigint,
    ENAME    CHAR(10),
    JOB      CHAR(9),
    MGR      bigint,
    HIREDATE DATE,
    SAL      bigint,
    COMM     bigint,
    DEPTNO   bigint,
    DNAME    VARCHAR(100),
    CONSTRAINT PK_EMP PRIMARY KEY (EMPNO)
);


insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7369, 'SMITH',  'CLERK',    7902, str_to_date('1980-12-17','%Y-%m-%d'), 800.00,  null,    20, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7499, 'ALLEN',  'SALESMAN', 7698, str_to_date('1981-02-20','%Y-%m-%d'), 1600.00, 300.00,  30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7521, 'WARD',   'SALESMAN', 7698, str_to_date('1981-02-22','%Y-%m-%d'), 1250.00, 500.00,  30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7566, 'JONES',  'MANAGER',  7839, str_to_date('1981-04-02','%Y-%m-%d'), 2975.00, null,    20, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7654, 'MARTIN', 'SALESMAN', 7698, str_to_date('1981-09-28','%Y-%m-%d'), 1250.00, 1400.00, 30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7698, 'BLAKE',  'MANAGER',  7839, str_to_date('1981-05-01','%Y-%m-%d'), 2850.00, null,    30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7782, 'CLARK',  'MANAGER',  7839, str_to_date('1981-06-09','%Y-%m-%d'), 2450.00, null,    10, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7788, 'SCOTT',  'ANALYST',  7566, str_to_date('1987-04-19','%Y-%m-%d'), 3000.00, null,    20, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7839, 'KING',   'PRESIDENT',null, str_to_date('1981-11-17','%Y-%m-%d'), 5000.00, null,    10, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7844, 'TURNER', 'SALESMAN', 7698, str_to_date('1981-09-08','%Y-%m-%d'), 1500.00, 0.00,    30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7876, 'ADAMS',  'CLERK',    7788, str_to_date('1987-05-23','%Y-%m-%d'), 1100.00, null,    20, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7900, 'JAMES',  'CLERK',    7698, str_to_date('1981-12-03','%Y-%m-%d'), 950.00,  null,    30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7902, 'FORD',   'ANALYST',  7566, str_to_date('1981-12-03','%Y-%m-%d'), 3000.00, null,    20, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7934, 'MILLER', 'CLERK',    7782, str_to_date('1982-01-23','%Y-%m-%d'), 1300.00, null,    10, null);


复制

1、str_to_date 函数

str_to_date 函数语法如下：

STR_TO_DATE(str, format)
复制

其中 str 是要转换的字符串，format 是日期格式，该格式用于解析 str 中的日期。这里有一些常用的日期格式符：

• %Y - 4位年份
• %m - 2位月份（01-12）
• %d - 2位日期（01-31）
• %H - 2位小时（00-23）
• %i - 2位分钟（00-59）
• %s - 2位秒（00-59）

假设我们的 emp 表有一个名为 birth_str 的列，其包含员工的生日字符串，例如 “1990-01-31”。要将该列转换为日期格式，可以使用以下查询：

SELECT EMPNO, STR_TO_DATE(HIREDATE, '%Y-%m-%d') as HIREDATE
 FROM EMP;
+-------+------------+
| EMPNO | HIREDATE   |
+-------+------------+
|  7369 | 1980-12-17 |
|  7499 | 1981-02-20 |
|  7521 | 1981-02-22 |
|  7566 | 1981-04-02 |
|  7654 | 1981-09-28 |
|  7698 | 1981-05-01 |
|  7782 | 1981-06-09 |
|  7788 | 1987-04-19 |
|  7839 | 1981-11-17 |
|  7844 | 1981-09-08 |
|  7876 | 1987-05-23 |
|  7900 | 1981-12-03 |
|  7902 | 1981-12-03 |
|  7934 | 1982-01-23 |
+-------+------------+
14 rows in set (0.00 sec)
复制

2、date_format函数

DATE_FORMAT(date, format)
复制

其中，date 是要转换的日期或日期时间值，format 是字符串格式，用于指定输出的日期字符串格式。日期格式符与 str_to_date 函数中的相同。
假设我们的 emp 表有一个名为 join_date 的列，其包含员工的入职日期，数据类型为 DATE。如果我们想将 join_date 以 “月-日-年” 的格式显示，可以使用以下查询：

SELECT empno, DATE_FORMAT(HIREDATE, '%m-%d-%Y') as formatted_join_date
FROM EMP;
+-------+---------------------+
| empno | formatted_join_date |
+-------+---------------------+
|  7369 | 12-17-1980          |
|  7499 | 02-20-1981          |
|  7521 | 02-22-1981          |
|  7566 | 04-02-1981          |
|  7654 | 09-28-1981          |
|  7698 | 05-01-1981          |
|  7782 | 06-09-1981          |
|  7788 | 04-19-1987          |
|  7839 | 11-17-1981          |
|  7844 | 09-08-1981          |
|  7876 | 05-23-1987          |
|  7900 | 12-03-1981          |
|  7902 | 12-03-1981          |
|  7934 | 01-23-1982          |
+-------+---------------------+
14 rows in set (0.00 sec)

复制

这将返回类似 “01-31-1990” 的格式。

你还可以使用多种格式组合来显示日期和时间。例如，如果 emp 表中有一个名为 event_timestamp 的列，其包含日期和时间，可以将其转换为 “年-月-日 时:分:秒” 的格式，如下：

SELECT empno, DATE_FORMAT(HIREDATE, '%Y-%m-%d %H:%i:%s') as formatted_event_timestamp
FROM EMP;
+-------+---------------------------+
| empno | formatted_event_timestamp |
+-------+---------------------------+
|  7369 | 1980-12-17 00:00:00       |
|  7499 | 1981-02-20 00:00:00       |
|  7521 | 1981-02-22 00:00:00       |
|  7566 | 1981-04-02 00:00:00       |
|  7654 | 1981-09-28 00:00:00       |
|  7698 | 1981-05-01 00:00:00       |
|  7782 | 1981-06-09 00:00:00       |
|  7788 | 1987-04-19 00:00:00       |
|  7839 | 1981-11-17 00:00:00       |
|  7844 | 1981-09-08 00:00:00       |
|  7876 | 1987-05-23 00:00:00       |
|  7900 | 1981-12-03 00:00:00       |
|  7902 | 1981-12-03 00:00:00       |
|  7934 | 1982-01-23 00:00:00       |
+-------+---------------------------+
14 rows in set (0.00 sec)
复制

这将返回类似 “1990-01-31 08:30:00” 的格式。