MySQL 中 DATE_FORMAT() 函数对日期格式化参数与JAVA中各种日期表示字母对比
项目中的统计报表作的很多,需求中有按周、月统计数据的。查看了Mysql的API,发现Date_format是格式化日期的,看了Date_format()的具体说明后就用这个函数按周统计,sql大致如下:
select DATE_FORMAT(check_date ,'%X-%V') dates
,avg(weight)/10000 weight
from ho_body
where user_id=295
and weight > 0
and (check_date between '2009-02-24' and '2010-02-24')
group by dates
查出的结果为:
dates weight
2009-30 80.9
2009-31 80.425
2009-32 80.76666667
2009-33 80.75384615
2009-34 80.8
2009-35 79.88
2009-36 80.06
2009-37 79.875
2009-38 79.075
2009-39 79.26666667
2009-40 79.16666667
2009-41 78.875
2009-42 78.33333333
2009-43 78.77272727
2009-44 77.625
2009-45 77.825
2009-46 77.575
2009-47 77.45
2009-48 81.25
2009-49 76.5
2009-50 77.83333333
2009-52 79.8
2010-02 79.2
%X | 年,其中的星期日是周的第一天,4 位,与 %V 使用 |
%x | 年,其中的星期一是周的第一天,4 位,与 %v 使用 |
%Y | 年,4 位 |
%y | 年,2 位 |
难道%X只能与%V一起用表示年-周,并且周日为一周开始,
%x与%v一起用,表示 年-周,周一为一周开始?
再看到前面的
%V | 周 (01-53) 星期日是一周的第一天,与 %X 使用 |
%v | 周 (01-53) 星期一是一周的第一天,与 %x 使用 |
以"月"为周期做数据统计
%M | 月名 |
%m | 月,数值(00-1 |
select distinct DATE_FORMAT(check_date ,'%Y-%m') c1
,avg(weight)/10000 wei
from ho_body
where user_id=295
and weight > 0
and (check_date between '2009-02-24' and '2009-10-31')
group by c1
结果展示:
c1 wei
2009-08 80.66388889
2009-09 79.70555556
2009-10 78.83714286
总结:
-- 以" 周/月 "为周期做数据统计
select DATE_FORMAT(check_date ,'%X-%V') -- 西方习惯, 以星期日为一周中的第一天
,DATE_FORMAT(check_date ,'%x-%v') -- 中国习惯, 以星期一为一周中的第一天
,DATE_FORMAT(check_date ,'%Y-%m')
Date_format可以使用的格式有:
格式 | 描述 |
%a | 缩写星期名 |
%b | 缩写月名 |
%c | 月,数值 |
%D | 带有英文前缀的月中的天 |
%d | 月的天,数值(00-31) |
%e | 月的天,数值(0-31) |
%f | 微妙 |
%H | 小时 (00-23) |
%h | 小时 (01-12) |
%I | 小时 (01-12) |
%i | 分钟,数值(00-59) |
%j | 年的天 (001-366) |
%k | 小时 (0-23) |
%l | 小时 (1-12) |
%M | 月名 |
%m | 月,数值(00-12) |
%p | AM 或 PM |
%r | 时间,12-小时(hh:mm:ss AM 或 PM) |
%S | 秒(00-59) |
%s | 秒(00-59) |
%T | 时间, 24-小时 (hh:mm:ss) |
%U | 周 (00-53) 星期日是一周的第一天 |
%u | 周 (00-53) 星期一是一周的第一天 |
%V | 周 (01-53) 星期日是一周的第一天,与 %X 使用 |
%v | 周 (01-53) 星期一是一周的第一天,与 %x 使用 |
%W | 星期名 |
%w | 周的天 (0=星期日, 6=星期六) |
%X | 年,其中的星期日是周的第一天,4 位,与 %V 使用 |
%x | 年,其中的星期一是周的第一天,4 位,与 %v 使用 |
%Y | 年,4 位 |
%y | 年,2 位 |
Mysql中还有另外几种返回日期的函数,如:
SELECT EXTRACT(YEAR_MONTH FROM datecolum ) -- 返回格式如200902
EXTRACT() 函数用于返回日期/时间的单独部分,比如年、月、日、小时、分钟等等。
语法
EXTRACT(unit FROM date)
date 参数是合法的日期表达式。unit 参数可以是下列的值:
Unit 值 |
MICROSECOND |
SECOND |
MINUTE |
HOUR |
DAY |
WEEK |
MONTH |
QUARTER |
YEAR |
SECOND_MICROSECOND |
MINUTE_MICROSECOND |
MINUTE_SECOND |
HOUR_MICROSECOND |
HOUR_SECOND |
HOUR_MINUTE |
DAY_MICROSECOND |
DAY_SECOND |
DAY_MINUTE |
DAY_HOUR |
YEAR_MONTH |
JAVA中各种日期表示字母
字母 日期或时间元素 表示 示例
| Era 标志符 | Text |
|
| 年 | Year |
|
| 年中的月份 | Month |
|
| 年中的周数 | Number |
|
| 月份中的周数 | Number |
|
| 年中的天数 | Number |
|
| 月份中的天数 | Number |
|
| 月份中的星期 | Number |
|
| 星期中的天数 | Text |
|
| Am/pm 标记 | Text |
|
| 一天中的小时数(0-23) | Number |
|
| 一天中的小时数(1-24) | Number |
|
| am/pm 中的小时数(0-11) | Number |
|
| am/pm 中的小时数(1-12) | Number |
|
| 小时中的分钟数 | Number |
|
| 分钟中的秒数 | Number |
|
| 毫秒数 | Number |
|
| 时区 | General time zone |
|
| 时区 | RFC 822 time zone |
|