mysql数据库的日期数据处理 date_format与str_to_date的使用细节

最新推荐文章于 2023-07-02 11:35:49 发布

lujx_1024

最新推荐文章于 2023-07-02 11:35:49 发布

阅读量4k

点赞数 1

分类专栏： mysql 文章标签： mysql date 转换

本文链接：https://blog.csdn.net/LJX_ahut/article/details/76850404

版权

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

首先建立一个简单的数据表，表名testDateFunction，表结构如下所示

+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
| time  | date    | YES  |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+

再向数据表中插入几条测试数据，由于本文重点是记录关于日期函数的用法，数据插入过程就不在赘述，表数据如下所示：

+----+------------+
| id | time       |
+----+------------+
|  1 | 2009-09-26 |
|  2 | 2017-01-03 |
|  3 | 2017-08-07 |
+----+------------+

一·将数据库中的date数据转换为我们想要的String类型

需要使用的函数是：date_format(date,format),其中，date是需要转化的日期数据，format定义的是转换格式。

例：

1.将日期数据按照年-月-日时：分：秒的格式输出

format格式：

年份

%Y ----->年份，显示4位，如2017；

%y ----->年份，显示2位，如2017；

select date_format(now(),'%Y'); ---> 2017
select date_format(now(),'%y'); ---> 17

月份

%m ----->月份阿拉伯数字显示，占两位字符，01-12；

%M ----->月份英文全文显示，January-December；

%b ----->月份英文缩略显示，Jan-Dec；

%c ----->月份阿拉伯数字显示，1-12；

select date_format(now(),'%Y-%m'); ---> 2017-08select date_format(now(),'%Y-%M'); ---> 2017-Augustselect date_format(now(),'%Y-%b'); ---> 2017-Augselect date_format(now(),'%Y-%c'); ---> 2017-8

日

%d ----->日阿拉伯显示：00-31

%D----->日带有英文后缀：1st-31th

%e----->日阿拉伯显示：1-31

%j ----->日这一天在这个年份的序号：001-366

select date_format(now(),'%Y-%m-%d'); ---> 2017-08-07select date_format(now(),'%Y-%m-%D'); ---> 2017-08-7thselect date_format(now(),'%Y-%m-%e'); ---> 2017-08-7select date_format(now(),'%Y-%m-%d %j'); ---> 2017-08-07 219 ps：表示2017年8月7日是2017年的第219天

时、分、秒

%H ----> 时显示格式：00-23

%h----> 时显示格式： 01-12

%I ----> 时显示格式：01-12

%k----> 时显示格式：0-23

%l ----> 时显示格式：1-12

select date_format(now(),'%Y-%m-%d %H'); ---> 2017-08-07 16 ps:下午4点
select date_format(now(),'%Y-%m-%d %h'); ---> 2017-08-07 04
select date_format(now(),'%Y-%m-%d %I'); ---> 2017-08-07 04
select date_format(now(),'%Y-%m-%d %k'); ---> 2017-08-07 16 ps:当小于10时，显示为1-9，而不是01-09
select date_format(now(),'%Y-%m-%d %l'); ---> 2017-08-07 4

分： %i：00-59

select date_format(now(),'%Y-%m-%d %H:%i'); ---> 2017-08-07 16:47 ps:下午4点47分

秒： %S：00-59 %s:00-59

select date_format(now(),'%Y-%m-%d %H:%i:%S'); ---> 2017-08-07 16:47:53 ps:下午4点47分53秒
select date_format(now(),'%Y-%m-%d %H:%i:%s'); ---> 2017-08-07 16:47:53 ps:下午4点47分53秒

微秒 %f

select date_format(now(),'%Y-%m-%d %H:%i:%S %f'); ---> 2017-08-07 16:54:22 000000

12/24小时制

AM/PM ------->%pselect date_format(now(),'%Y-%m-%d %H:%i:%S %p'); ---> 2017-08-07 16:57:21 PM12小时制时间：%r: select date_format(now(),'%Y-%m-%d %r'); ---> 2017-08-07 04:57:59 PM 24小时制时间： %T： 14:02:44select date_format(now(),'%Y-%m-%d %T'); ---> 2017-08-07 17:15:16

周

%W ----> 周的英文显示 monday-sunday

%w ----> 周的阿拉伯显示：0（星期日）-6（星期六）

%a ----> 周的英文缩略显示：Mon-sun

select date_format(now(),'%Y-%m-%d %H:%i:%s %W'); ---> 2017-08-07 17:19:15 Mondayselect date_format(now(),'%Y-%m-%d %H:%i:%s %w'); ---> 2017-08-07 17:20:13 1 ps:1表示周一select date_format(now(),'%Y-%m-%d %H:%i:%s %a'); ---> 2017-08-07 17:19:15 Mon

二·将字符串类型转换为date类型

使用函数str_to_date (str,format),其中，format格式依然如同上述所示。

值得注意的是，str的格式需要和format的格式保持一致，例如 str_to_date('2017-08-07 16:56:12'，‘%Y-%m-%d %H:%i:%s’),若是前后不一致则会报错。不再赘述。