赞
踩
MySQL 的 datetime等日期和时间处理SQL函数及格式化显示
MySQL 时间相关的SQL函数:
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)
MySQL的SQL Date函数
NOW():返回当前的日期和时间
CURDATE():返回当前的日期
CURTIME():返回当前的时间
DATE():提取日期或日期/时间表达式的日期部分
EXTRACT():返回日期/时间按的单独部分
DATE_ADD():给日期添加指定的时间间隔
DATE_SUB():从日期减去指定的时间间隔
DATEDIFF():返回两个日期之间的天数
DATE_FORMAT():用不同的格式显示日期/时间
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)
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。