0


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)

  1. MySQL的SQL Date函数

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

CURDATE():返回当前的日期

CURTIME():返回当前的时间

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

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

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

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

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

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

  1. 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)

标签: mysql 数据库 java

本文转载自: https://blog.csdn.net/sunny05296/article/details/129024077
版权归原作者 sunny05296 所有, 如有侵权,请联系我们删除。

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

还没有评论