CODEWITHSUNDEEP
mysqlsqldate
user1437251
user1437251

Reputation: 309

SQL Query to get data between two weeks?

I have a week column with week numbers as w0, w1, w2.... I am trying to get last last six weeks data. Here's the sql query I am using.

SELECT * FROM week 
WHERE uid = '9df984da-4318-1035-9589-493e89385fad'       
  AND report_week BETWEEN `'w52' AND 'w5'`;

'w52' is essentially week 52 in December 2015 and 'w5' is Jan 2016. The 'between' seems to not work. Whats the best way to get data from the above two weeks?

Here's the CREATE TABLE statement:

CREATE TABLE `week` (`uid` VARCHAR(255) DEFAULT '' NOT NULL,
                     `report_week` VARCHAR(7) NOT NULL,
                     `report_files_active` BIGINT DEFAULT NULL);

Essentially this table is getting populated from other table which has date column. It uses dates from other table and summarizes weekly data into this.

Any help is appreciated.

Upvotes: 0

Views: 866

Answers (2)

Prahalad Deshpande
Prahalad Deshpande

Reputation: 4767

Refer to this SO Discussion which details the reasons for a problem similar to yours.

BETWEEN 'a' and 'b' actually matches to columnValue >='a' and columnValue <= 'b'

In your case w52 is greater than w5 due to lexicographic ordering of Strings - this means that the BETWEEN clause will never return a true (think about it as equivalent to saying BETWEEN 10 and 1 instead of BETWEEN 1 and 10.

Edit to my response:

Refrain from storing the week value as a string. Instead here are a couple of approaches in order of their preference:

  1. Have a timestamp column. You can easily then use MySQL query facilities to extract the week information out of this. For a reference see this post.
  2. Maintain two columns - YEAR, WEEKNO where YEAR will store values like 2015, 2016 etc and WEEKNO will store the week number. This way you can query data for any week in any year.

Upvotes: 1

Saurabh
Saurabh

Reputation: 51

please show me table structure and DB name because it different for other, if it is any timestamp then we can use BETWEEN 'systemdate' AND 'systemdate-6'

Upvotes: 0

Related Questions