精华内容
下载资源
问答
  • 解决方案 Have a look at this example from WEEK function description: mysql> SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0); -> 2000, 0 One might argue that WEEK() should return 52 because the given ...

    This simple query does not seem to be outputting the correct results. Given that the mode is the same (1) for all the functions shouldn't "week("2018-12-31", 1)" show a value of 1 rather than 53?

    SELECT yearweek("2018-12-31", 1), yearweek("2019-01-02", 1),

    week("2018-12-31", 1), week("2019-01-02", 1)

    which outputs the following.

    '201901','201901','53','1'

    Is there a default dev more that I need to set somewhere?

    解决方案

    Have a look at this example from WEEK function description:

    mysql> SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0);

    -> 2000, 0

    One might argue that WEEK() should return 52 because the given date

    actually occurs in the 52nd week of 1999. WEEK() returns 0 instead so

    that the return value is “the week number in the given year.”

    The above seem to suggest that all modes having 0-53 range would return the week number relative to the year of input date (it'll return 0 if the date falls in the last week of the previous year).

    So if first day of year = Monday and first week of year = having 4 or more days this year, then 2018-12-31 belongs to 53rd week of 2018 -and- 1st week of 2019, and the mode parameter determines the return value:

    SELECT WEEK('2018-12-31', 1); -- 53

    SELECT WEEK('2018-12-31', 3); -- 1

    The YEARWEEK function is unambiguous (the result includes the year) so above does not apply.

    展开全文
  • Can someone kindly explain me why these two give different results?I execute this with PHP.date("YW",mktime(0, 0, 0, 3, 22 , 2013... // outputs 201312And when I execute this with MySQLSELECT YEARWEEK(...

    Can someone kindly explain me why these two give different results?

    I execute this with PHP.

    date("YW",mktime(0, 0, 0, 3, 22 , 2013)); // outputs 201312

    And when I execute this with MySQL

    SELECT YEARWEEK(now()); // outputs 201311

    解决方案

    You need to specify mode 3 on the mysql YEARWEEK call:

    SELECT YEARWEEK(now(),3);

    The PHP date() placeholder W returns the week number according to the ISO 8601 specification. That means weeks start on Monday (not Sunday), the first week of the year is number 1 (not 0), and that week is the first one that has at least 4 days in the new year (so it's the week containing the new year's first Thursday). According to the documentation for the MySQL WEEK function, that combination of options is mode 3.

    Also, to pull Alles's note into the accepted answer because it's important: the placeholders Y and W don't go together. If you want the year that goes with the ISO week number, you should use o instead of Y. For example, consider Monday, December 29th, 2014:

    date('YW', mktime(0,0,0,12,29,2014)); #=> 201401 : 1st week of 2014??

    date('oW', mktime(0,0,0,12,29,2014)); #=> 201501 : better

    展开全文
  • mysql YEARWEEK(date[,mode]) 函数 查询上周数据 通常使用下边sql即可(如果数据库设置了周一为一周起始的话): -- 查询上周数据(addtime为datetime格式) SELECT id,addtime FROM mall_order WHERE YEARWEEK(date_...

    mysql YEARWEEK(date[,mode]) 函数 查询上周数据

    通常使用下边sql即可(如果数据库设置了周一为一周起始的话):

    -- 查询上周数据(addtime为datetime格式)
    SELECT id,addtime FROM mall_order WHERE YEARWEEK(date_format(addtime,'%Y-%m-%d')) = YEARWEEK(now())-1; 
    

    YEARWEEK(date[,mode]) 函数默认西方周日当做一周的起始,需要改成国人的使用习惯,用周一作为一周起始,这里设置YEARWEEK的第二个参数mode为1即可。

    由于每个数据库的配置不一样,所以最好还是先执行下边的sql语句人工审查一下数据结果的第一条和最后一条数据的时间是否是上周的周一和周末,再考虑是否给mode赋值

    -- 查询上周数据(addtime为时间戳) 
    SELECT id,addtime FROM mall_order WHERE YEARWEEK(FROM_UNIXTIME(addtime,'%Y-%m-%d'),1) = YEARWEEK(now(),1)-1; 
    -- 查询上周数据(addtime为datetime格式)
    SELECT id,addtime FROM mall_order WHERE YEARWEEK(date_format(addtime,'%Y-%m-%d'),1) = YEARWEEK(now(),1)-1; 
    

    补充:

    -- 查询当前这周的数据 
    SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());
     
    -- 查询上周的数据
    SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;
     
     
    -- 查询当前月份的数据
    select name,submittime from enterprise  where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')
    
    --查询上个月的数据
    select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m');
    select * from `user` where DATE_FORMAT(pudate,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') ;
    
    --查询距离当前现在6个月的数据
    select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();
    
    展开全文
  • MySQLyearweek函数默认是从周日~周六,需求需要从周一到周日,看了MySQL的文档后,按照如下使用即可更改开始日期。 http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_week 形...

    MySQL 的yearweek函数默认是从周日~周六,需求需要从周一到周日,看了MySQL的文档后,按照如下使用即可更改开始日期。
     
    形如:
    AND yearweek(from_unixtime(createTime/1000, '%Y-%m-%d'), 1) = yearweek(#{dayTime}, 1) 

    转载于:https://www.cnblogs.com/asfeixue/p/4871397.html

    展开全文
  • MySQL yearweek的用法

    2011-08-09 23:17:10
    MySQLYEARWEEK 是获取年份和周数的一个函数,函数形式为 YEARWEEK(date[,mode]) 例如 2010-3-14 ,礼拜天 SELECT YEARWEEK('2010-3-14') 返回 11SELECT YEARWEEK('2010-3-14',1) 返回 10 其中第二个参数是 ...
  • 2019独角兽企业重金招聘Python工程师标准>>> ...select yearweek('2016-01-03',1); DATE_FORMAT同样效果的写法: SELECT date_format( '2016-01-03 09:16:18', '%x-%v' ) 参考链接 ...
  • mysql yearweek 函数周日当做一周开始

    千次阅读 2018-01-06 15:26:53
    统计数据库内一周数据时用到 yearweek 函数,查询出来的结果与预期值有较大差距, 原来mysql默认 周日当做一周开始,修改成周一当做开始需要在yearweek函数后的model里面填值1即可
  • 通常使用下边sql即可(如果数据库设置了周一为一周起始的话): -- 查询上周数据(addtime为datetime... YEARWEEK(FROM_UNIXTIME(activate_day,'%Y-%m-%d'),1)=YEARWEEK(NOW(),1) YEARWEEK(date[,mode]) 函数...
  • 通常使用下边sql即可(如果数据库设置了周一为一周起始的话)... SELECT id,addtime FROM mall_order WHERE YEARWEEK(date_format(addtime,'%Y-%m-%d')) = YEARWEEK(now())-1; YEARWEEK(date[,mode]) 函数默认西方...
  • mysql_yearweek 用于获取与 MySQL 函数返回的完全相同的 yearweek 的 gem。 概述 MySQLyearweek函数有八种不同的模式来确定 yearweek。 这将为每种模式获得相同的 yearweek,其中4是默认模式。 该 gem 已针对 ...
  • mysqlyearweek和DATE_FORMAT

    千次阅读 2018-08-09 10:43:07
    php date函数 'W' 获取到的星期数,经测试发现相当于mysql yearweek函数的第二个参数为3的获取的值 -----------------------------------------------------------------------分割线-----------------------------...
  • MySQLYEARWEEK函数用法

    千次阅读 2017-11-27 17:16:03
    MySQLYEARWEEK 是获取年份和周数的一个函数,函数形式为 YEARWEEK(date[,mode])  例如 2010-3-14 ,礼拜天  SELECT YEARWEEK('2010-3-14') 返回 11  SELECT YEARWEEK('2010-3-14',1) 返回 10  查询当前...
  • mysqlyearweek 和 weekofyear函数

    万次阅读 2016-03-22 10:42:20
    1.MySQLYEARWEEK 是获取年份和周数的一个函数,函数形式为YEARWEEK(date[,mode]) 例如 2010-3-14 ,礼拜天 SELECT YEARWEEK('2010-3-14') 返回 11 SELECT YEARWEEK('2010-3-14',1) 返回 10 其中第二个参数是 ...
  • I've a YEARWEEK() converted value from MySQL201439I want to convert it back to the starting date of that week number, using MySQL, possible?e.g.SELECT xxx('201439');and return'2014-09-16'解决方案e.g. ...
  • 需求要实现按周统计某个数量,本来以为调用mysql的...mysql还好,有yearWeek方法,而java根本没有类似的方法calendar.get(Calendar.WEEK_OF_YEAR)根本没有包含年份信息,这一点也是比较奇怪,因为单独使用calendar.ge
  • MySQLYEARWEEK函数以及查询本周数据 MySQLYEARWEEK 是获取年份和周数的一个函数,函数形式为 YEARWEEK(date[,mode])例如 2010-3-14 ,礼拜天SELECT YEARWEEK('2010-3-14') 返回 11SELECT YEARWEEK('2010-3-14...
  • mysqlyearweek()函数转换与逆转换。

    千次阅读 2015-07-29 10:27:26
    在sql开发中,经常会有按周进行group by,在将date处理成周时,mysql提供了很多方便的函数,例如week(),yearweek()等。 其中yearweek()是包含年份的week()函数,能更方便的进行聚合。 这次就提供一种对该函数的...
  • MySQLYEARWEEK函数以及查询本周数据

    千次阅读 2014-02-16 16:25:19
    http://www.2cto.com/database/201303/194301.html
  • MySQLYEARWEEK 是获取年份和周数的一个函数,函数形式为 YEARWEEK(date[,mode]) 例如 2010-3-14 ,礼拜天 SELECT YEARWEEK('2010-3-14') 返回 11 SELECT YEARWEEK('2010-3-14',1) 返回 10 其中第二...
  • 公司的一个报表需要按周进行数据的统计汇总,在mysql中使用yearweek()函数将时间转化为年份和周数 SELECT YEARWEEK('2000-01-01'); -> 199952 在代码中使用Calendar获取时间是今年的第几周 //获取传入时间的...
  • 用法 : yearweek(date,[mode]),mode可选填,默认为以周日为当前周的第一天 可选参数: Mode First day of week Range Week 1 is the first week … 0 Sunday 0-53 with a Sunday in this ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 2,992
精华内容 1,196
关键字:

mysqlyearweek

mysql 订阅