CODEWITHSUNDEEP
mysqlselect
Derple
Derple

Reputation: 863

MySQL compare by week

I have a select statement of which needs to base its WHERE on a timestamp but for all dates within that week beginning monday.

The MySQL

SELECT DISTINCT(unique_reference) AS unique_reference,  date(datetime) AS datetime
    FROM sales_tickets

    WHERE Date(datetime)='$datetime'

This is based on the fact that $datetime can be any date but the select statement needs to get all records from that week, example: if its the Monday 12th May 2014, it will fetch all results from that week, instead of the one day.

Currently, its fetching only one day of results.

I have no idea how to rectify this issue. Any advise would be awesome thanks.

Upvotes: 3

Views: 2473

Answers (4)

GHugo
GHugo

Reputation: 2654

You can compare using the WEEK function :

WHERE WEEK(DATE(datetime)) = WEEK('$datetime')

If you have multiples years for entries, you can use instead the YEARWEEK function.

Edit for first day of week: WEEK and YEARWEEK functions have both a second optional argument which tells when a week start. Try to consider mode 1 or 5.

Mode    First day of week   Range   Week 1 is the first week …
0   Sunday  0-53    with a Sunday in this year
1   Monday  0-53    with 4 or more days this year
2   Sunday  1-53    with a Sunday in this year
3   Monday  1-53    with 4 or more days this year
4   Sunday  0-53    with 4 or more days this year
5   Monday  0-53    with a Monday in this year
6   Sunday  1-53    with 4 or more days this year
7   Monday  1-53    with a Monday in this year

Upvotes: 3

Loopo
Loopo

Reputation: 2195

The easiest method might be to have your WHERE statement check against a range of values, which you can calculate beforehand. I'll assume you're using php.

So your SQL statement will be:

SELECT DISTINCT(unique_reference) AS unique_reference,  date(datetime) AS datetime
FROM sales_tickets

WHERE (Date(datetime) > '$startDate')
   AND (Date(datetime) < '$endDate');

You'll first have to figure out what $startDate and $endDate are:

$endDate = strtotime('Monday', time()); // might need to adjust this depending on when your week starts 
$startDate = $endDate - 7*24*60*60; //one week before.

Be careful with converting times between unix timestamps and datetimes used in SQL, but you get the idea.

Upvotes: 1

eggyal
eggyal

Reputation: 125865

A sargable solution would explicitly calculate the start and end points of your desired range:

WHERE datetime >= DATE('$datetime') + INTERVAL 0 - WEEKDAY('$datetime') DAY
  AND datetime <  DATE('$datetime') + INTERVAL 7 - WEEKDAY('$datetime') DAY

Upvotes: 2

user3030212
user3030212

Reputation: 439

Try WEEK():

WHERE WEEK(datetime)=WEEK('$datetime')

Read more: WEEK()

Upvotes: 0

Related Questions