mysql group by week, based on int dateid(yyyymmdd)

Question

I have read several questions like MySQL grouping by week, based on a date column?.

But my date type is int(11)(I can't change the structure) to store something like 20150101 to represent 1st Jan,2015. How should I use WEEK method to group by based on this?

Like:

select week(SOMETHING_TO(dateid)) as weeknum, game ,count(*) 
from table where game is not null group by weeknum , game order by weeknum desc;

score 1 · Accepted Answer · edited Mar 18 '15 at 09:26

1

I think you need to first change your int(11) value to date and then use the Week function ie,

select WEEK(DATE(dateid)) as weeknum, game ,count(*) 
from table

DEMO

EDIT:-

As per your comments you may try to use

select concat('last ' ,(WEEK(dateid,1) -WEEK(CURDATE(),1)),' week',' ' )

DEMO

Also check the WEEK function in detail.

The two-argument form of WEEK() enables you to specify whether the week starts on Sunday or Monday and whether the return value should be in the range from 0 to 53 or from 1 to 53. If the mode argument is omitted, the value of the default_week_format system variable is used.

Also see this list for reference:

M   F.day   Range   Week 1 is the first week …
0   Sunday  0-53    with a Sunday in this year
1   Monday  0-53    with more than 3 days this year
2   Sunday  1-53    with a Sunday in this year
3   Monday  1-53    with more than 3 days this year
4   Sunday  0-53    with more than 3 days this year
5   Monday  0-53    with a Monday in this year
6   Sunday  1-53    with more than 3 days this year
7   Monday  1-53    with a Monday in this year

M = Mode
F.day = First day of week

edited Mar 18 '15 at 09:26

JaskeyLam

14,373
17
110
142

answered Mar 18 '15 at 06:45

Rahul Tripathi

161,154
30
262
319

I can't change the structure of the db – JaskeyLam Mar 18 '15 at 06:46
@Jaskey:- You dont have to change the structure for this. I am asking you to use the FROM_UNIXTIME function – Rahul Tripathi Mar 18 '15 at 06:49
But I had tried something like : FROM_UNIXTIME(20150101) , it returns 1970-08-22 14:15:01... – JaskeyLam Mar 18 '15 at 06:52
@Jaskey:- Updated my answer. Initially I thought that the dateid is an integer. But your int column is in a dateformat so you need a DATE fucntion for it. Please try the updated query. – Rahul Tripathi Mar 18 '15 at 07:06
Yes, it should work. Would you please update you answer it a little bit so that i shows "last n week" as the weeknum since it will be more helpful to others and I will accept your answer – JaskeyLam Mar 18 '15 at 07:08
Your description says you are changing it to `datetime` but you are actually changing it to `date`. Minor technicality, but for clarity's sake... – Peter Bowers Mar 18 '15 at 07:14
@PeterBowers:- Updated that. Thanks! – Rahul Tripathi Mar 18 '15 at 08:30
@Jaskey:- I am not sure if I got that clearly. Although updated my answer a bit – Rahul Tripathi Mar 18 '15 at 08:32
@RahulTripathi, actually I mean `select concat('last ' ,(WEEK(dateid,1) -WEEK(CURDATE(),1)),' day',' ' ) `, please also inform the reader that the `WEEK` function begins from SUNDAY by default so if I want it from Monday . I should use WEEK(date, 1) – JaskeyLam Mar 18 '15 at 09:07
@Jaskey:- Good point, however feel free to to update or edit the answer! – Rahul Tripathi Mar 18 '15 at 09:07
@Jaskey:- Updated the answer. :) – Rahul Tripathi Mar 18 '15 at 09:11

mysql group by week, based on int dateid(yyyymmdd)

1 Answers1