[MDEV-5729] The yearweek() and week() functions return a wrong value Created: 2014-02-25  Updated: 2017-12-30  Resolved: 2017-12-30

Status: Closed
Project: MariaDB Server
Component/s: Temporal Types
Affects Version/s: 5.5.33a
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Leif Inge Sandberg Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: upstream
Environment:

Linux


 Description   

select yearweek('2014-02-25') - returns 201408 instead of 201409
select week('2014-02-25') - returns 8 instead of 9

It seems to be caused by incorrectly using week 201253 instead of week 201301. All dates I have tried after this week report one week too low.

select yearweek('2012-12-31') - returns 201253 instead of 201301

We report weekly statistics and now just discovered that all of 2013 and 2014 are one week off. Using Microsoft SQL we get the correct week numbers, but that's not an option right now.

 Comments   
Comment by Leif Inge Sandberg [ 2014-02-25 ]

Note the discussion here; the behaviour of the week() and yearweek() functions is designed to be dependent on the default_week_format system variable.

http://bugs.mysql.com/bug.php?id=71827

Comment by Cristian Nicoara [ 2014-02-26 ]
  • the same behavior is in 5.5.35-MariaDB 

    MariaDB [(none)]> select yearweek('2014-02-25')\G
    		 
    yearweek('2014-02-25'): 201408
    		 
    1 row in set (0.00 sec)

  • but we have the same behavior on mysql 5.1.49-3 as well. 

    select yearweek('2014-02-25')\G
    		 
    yearweek('2014-02-25'): 201408
    		 
    1 row in set (0.00 sec)
    		 
     
    		 
    select yearweek('2014-01-02')\G
    		 
    yearweek('2014-01-02'): 201352
    		 
    1 row in set (0.00 sec)

But I do not know if this is a bug or a feature ; because days 1 until 4 Jan 2014 are from the same week as 29 until 31 Dec 2013 ...

Comment by Cristian Nicoara [ 2014-02-26 ]

And ...

by using this approach we can change the behavior :

MariaDB [(none)]> select yearweek('2014-01-01',1)\G
yearweek('2014-01-01',1): 201401

Comment by Leif Inge Sandberg [ 2014-02-26 ]

True; by using the mode parameter we get the standard ISO result, as mentioned in the MySQL bug above. I still view this as a bug, because the default_week_format system variable seems to have no effect. The mode parameter, however, is a workaround that makes this bug a minor one.

Comment by Daniel Black [ 2017-12-30 ]

MySQL upstream bug resolved this as a documentation bug. MariaDB kb for yearweek describe the behaviour similar to week in the same way as upstream. As we can't logically create different behaviour this can be closed.

Generated at Thu Feb 08 07:06:37 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.