MySQL 的 datetime等日期和时间处理SQL函数及格式化显示

MySQL 的 datetime等日期和时间处理SQL函数及格式化显示

MySQL 时间相关的SQL函数：

1. MySQL的SQL DATE_FORMAT函数：

用于以不同的格式显示日期/时间数据。

DATE_FORMAT(date, format) 根据格式串 format 格式化日期或日期和时间值 date，返回结果串。

可用使用 DATE_FORMAT() 来格式化 DATE、DATETIME、TIMESTAMP的值以期望的格式进行显示。

DATE_FORMAT 支持的格式如下：

%S，%s：两位数字形式的秒（00~59）

%i：两位数字形式的分（00~59）

%H：两位数字形式的小时，24小时制（00~23）

%h, %I：两位数字形式的小时，12小时制（01~12）

%k：数字形式的小时，24小时制（0,1,...,23）

%l：数字形式的小时，12小时（1,2,...,12）

%T：24小时的时间形式（hh:mm:ss）

%r：12小时的时间形式（hh:mm:ss AM 或hh:mm:ss PM）

%p：AM 或P M

%W：一周中每一天的名称（Sunday,Monday,...,Saturday）

%a：一周中每一天名称的缩写（Sun,Mon,...,Sat）

%d：两位数字表示月中的天数（00,01,...,31）

%e：数字形式表示月中的天数（1,2,...,31）

%D：英文后缀表示月中的天数（1st,2nd,3rd,...）

%w：以数字形式表示周中的天数（0=Sunday,1=Monday,...,6=Saturday）

%j：以三位数字表示年中的天数（001,002,...,366）

%U：周（0,1,...,52），其中Sunday为周中的第一天

%u：周（0,1,...,52)，其中Monday为周中的第一天

%M：月（January,February,...,December）

%b：缩写的月（Jan,Feb,...,Dec）

%m：两位数字表示的月份（01,02,...,12）

%c：数字表示的月份（1,2,...,12）

%Y：四位数字表示的年份（2023）

%y：两位数字表示的年份（23）

%%：直接值“%”

测试：

select date_format(current_timestamp(), '%Y-%m-%d %H:%i:%s.%f'); -- 不指定精度、默认精确到秒

select date_format(current_timestamp(6), '%Y-%m-%d %H:%i:%s.%f'); -- 指定精度到最大精度微秒

select date_format(now(), '%Y-%m-%d %H:%i:%s.%f'); -- 不指定精度、默认精确到秒

select date_format(now(6), '%Y-%m-%d %H:%i:%s.%f'); -- 指定精度到最大精度微秒

mysql> select date_format(current_timestamp(), '%Y-%m-%d %H:%i:%s.%f'); -- 不指定精度、默认精确到秒

+----------------------------------------------------------+

| date_format(current_timestamp(), '%Y-%m-%d %H:%i:%s.%f') |

+----------------------------------------------------------+

| 2023-02-14 10:40:38.000000 |

+----------------------------------------------------------+

1 row in set (0.00 sec)

mysql> select date_format(current_timestamp(6), '%Y-%m-%d %H:%i:%s.%f'); -- 指定精度到最大精度微秒

+-----------------------------------------------------------+

| date_format(current_timestamp(6), '%Y-%m-%d %H:%i:%s.%f') |

+-----------------------------------------------------------+

| 2023-02-14 10:40:52.119296 |

+-----------------------------------------------------------+

1 row in set (0.00 sec)

mysql> select date_format(now(), '%Y-%m-%d %H:%i:%s.%f'); -- 不指定精度、默认精确到秒

+--------------------------------------------+

| date_format(now(), '%Y-%m-%d %H:%i:%s.%f') |

+--------------------------------------------+

| 2023-02-14 10:40:56.000000 |

+--------------------------------------------+

1 row in set (0.00 sec)

mysql> select date_format(now(6), '%Y-%m-%d %H:%i:%s.%f'); -- 指定精度到最大精度微秒

+---------------------------------------------+

| date_format(now(6), '%Y-%m-%d %H:%i:%s.%f') |

+---------------------------------------------+

| 2023-02-14 10:41:00.558442 |

+---------------------------------------------+

1 row in set (0.00 sec)

mysql> select date_format(DATE_ADD(now(6), INTERVAL 2 DAY), '%Y-%m-%d %H:%i:%s.%f'); -- 指定精度到最大精度微秒，日期+2天

+-----------------------------------------------------------------------+

| date_format(DATE_ADD(now(6), INTERVAL 2 DAY), '%Y-%m-%d %H:%i:%s.%f') |

+-----------------------------------------------------------------------+

| 2023-02-16 10:41:08.324454 |

+-----------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> select date_format(DATE_SUB(now(6), INTERVAL 2 DAY), '%Y-%m-%d %H:%i:%s.%f'); -- 指定精度到最大精度微秒，日期-2天

+-----------------------------------------------------------------------+

| date_format(DATE_SUB(now(6), INTERVAL 2 DAY), '%Y-%m-%d %H:%i:%s.%f') |

+-----------------------------------------------------------------------+

| 2023-02-12 10:41:15.232208 |

+-----------------------------------------------------------------------+

1 row in set (0.00 sec)

SQL插入datetime类型的数据到数据表中：

test01的表结构：

mysql> show create table test01;

+-----------+---------------------------------------------------+

| Table | Create Table |

+-----------+---------------------------------------------------+

| test01 | CREATE TABLE `test01` (

`id` bigint(20) NOT NULL AUTO_INCREMENT,

`c_datetime` datetime DEFAULT NULL,

`c_timestamp` timestamp NULL DEFAULT NULL,

`c_datetime6` datetime(6) DEFAULT NULL,

`c_datetime3` datetime(3) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 |

+-----------+---------------------------------------------------+

1 row in set (0.00 sec)

插入数据：

mysql> insert into test01(id,c_datetime,c_timestamp,c_datetime6,c_datetime3) values (

7,

date_format(current_timestamp(6), '%Y-%m-%d %H:%i:%s.%f'),

date_format(current_timestamp(6), '%Y-%m-%d %H:%i:%s.%f'),

date_format(current_timestamp(6), '%Y-%m-%d %H:%i:%s.%f'),

date_format(current_timestamp(6), '%Y-%m-%d %H:%i:%s.%f'));

mysql> insert into test01(id,c_datetime,c_timestamp,c_datetime6,c_datetime3) values (

8,

date_format(now(6), '%Y-%m-%d %H:%i:%s.%f'),

date_format(now(6), '%Y-%m-%d %H:%i:%s.%f'),

date_format(now(6), '%Y-%m-%d %H:%i:%s.%f'),

date_format(now(6), '%Y-%m-%d %H:%i:%s.%f'));

mysql> insert into test01(id,c_datetime,c_timestamp,c_datetime6,c_datetime3) values (

9,

'2023-01-01 15:59:30.123456',

'2023-01-01 15:59:30.123456',

'2023-01-01 15:59:30.123456',

'2023-01-01 15:59:30.123456');

mysql> insert into test01(id,c_datetime,c_timestamp,c_datetime6,c_datetime3) values (

10,

now(6),

now(6),

now(6),

current_timestamp(6));

mysql> select * from test01;

+----+---------------------+---------------------+----------------------------+-------------------------+

| id | c_datetime | c_timestamp | c_datetime6 | c_datetime3 |

+----+---------------------+---------------------+----------------------------+-------------------------+

| 1 | 2023-02-02 09:10:11 | NULL | NULL | NULL |

| 2 | 2023-02-02 09:10:15 | NULL | NULL | NULL |

| 3 | 2038-01-01 15:00:01 | 2038-01-01 15:00:01 | NULL | NULL |

| 4 | 2038-01-05 15:00:01 | 2038-01-05 15:00:01 | NULL | NULL |

| 5 | 2038-01-30 15:00:01 | NULL | NULL | NULL |

| 6 | 9999-01-01 15:00:01 | NULL | 9999-01-01 15:00:01.111222 | 9999-01-01 15:00:01.111 |

| 7 | 2023-02-14 09:31:14 | 2023-02-14 09:31:14 | 2023-02-14 09:31:13.946331 | 2023-02-14 09:31:13.946 |

| 8 | 2023-02-14 10:47:47 | 2023-02-14 10:47:47 | 2023-02-14 10:47:47.168735 | 2023-02-14 10:47:47.169 |

| 9 | 2023-01-01 15:59:30 | 2023-01-01 15:59:30 | 2023-01-01 15:59:30.123456 | 2023-01-01 15:59:30.123 |

| 10 | 2023-02-14 10:58:13 | 2023-02-14 10:58:13 | 2023-02-14 10:58:13.141804 | 2023-02-14 10:58:13.142 |

+----+---------------------+---------------------+----------------------------+-------------------------+

10 rows in set (0.06 sec)

mysql> select id,date_format(c_datetime6, '%Y-%m-%d %H:%i:%s') from test01;

+----+-----------------------------------------------+

| id | date_format(c_datetime6, '%Y-%m-%d %H:%i:%s') |

+----+-----------------------------------------------+

| 1 | NULL |

| 2 | NULL |

| 3 | NULL |

| 4 | NULL |

| 5 | NULL |

| 6 | 9999-01-01 15:00:01 |

| 7 | 2023-02-14 09:31:13 |

| 8 | 2023-02-14 10:47:47 |

| 9 | 2023-01-01 15:59:30 |

| 10 | 2023-02-14 10:58:13 |

+----+-----------------------------------------------+

10 rows in set (0.00 sec)

mysql> select id,date(c_datetime6) from test01;

+----+-------------------+

| id | date(c_datetime6) |

+----+-------------------+

| 1 | NULL |

| 2 | NULL |

| 3 | NULL |

| 4 | NULL |

| 5 | NULL |

| 6 | 9999-01-01 |

| 7 | 2023-02-14 |

| 8 | 2023-02-14 |

| 9 | 2023-01-01 |

| 10 | 2023-02-14 |

+----+-------------------+

10 rows in set (0.01 sec)

mysql> select id,datediff(c_datetime6, '2020-01-01') from test01;

+----+-------------------------------------+

| id | datediff(c_datetime6, '2020-01-01') |

+----+-------------------------------------+

| 1 | NULL |

| 2 | NULL |

| 3 | NULL |

| 4 | NULL |

| 5 | NULL |

| 6 | 2914270 |

| 7 | 1140 |

| 8 | 1140 |

| 9 | 1096 |

| 10 | 1140 |

+----+-------------------------------------+

10 rows in set (0.00 sec)

2. MySQL的SQL Date函数

NOW()：返回当前的日期和时间

CURDATE()：返回当前的日期

CURTIME()：返回当前的时间

DATE()：提取日期或日期/时间表达式的日期部分

EXTRACT()：返回日期/时间按的单独部分

DATE_ADD()：给日期添加指定的时间间隔

DATE_SUB()：从日期减去指定的时间间隔

DATEDIFF()：返回两个日期之间的天数

DATE_FORMAT()：用不同的格式显示日期/时间

3. MySQL的SQL Date 数据类型

MySQL 使用下列数据类型在数据库中存储日期或日期/时间值：

DATE - 格式 YYYY-MM-DD

DATETIME - 格式: YYYY-MM-DD HH:MM:SS

TIMESTAMP - 格式: YYYY-MM-DD HH:MM:SS

YEAR - 格式 YYYY 或 YY

DATETIME和TIMESTAMP如果要精确到毫秒或微妙，需要指定精度，例如：DATETIME(6)、TIMESTAMP(6)