Wondered if anyone could shed some light on how to go about doing this.
I need to get the distinct date is Sat 3rd June from the DB which is stored as a timestamp and its ID for say the next 7 days from today
ie 4th, 5th, 6th…
There are approx 500,000 records with a mixture of dates from now until several month ahead. I’ve tried using SELECT DISTINCT date but realised the timestamps are all different even though
many are the same date. Also wonder if the DISTINCT is going to be really slow to access even though I have set and index on it.
ok, i’ve not explained this very well. I have a table with around 500,000. These records contain date and order details. I need to display the orders and date for say next seven days like
Sat 3rd June
Sun 4th June
The order date is stored as a timestamp for that particular order time.
I can’t used SELECT DISTINCT orderDate FROM tbl WHERE orderDate>=$today AND orderDate<=$sevenDaysFromNow as the timestamps are not exactly the same as they will have ordered at different times during the day. I can’t think how to split the days and show the orders for that specific day.
select id, date(orderDate) as day
select id, orderDate
where orderDate between UNIX_TIMESTAMP(CURRENT_DATE) and UNIX_TIMESTAMP(CURRENT_DATE + INTERVAL 6 DAY)
order by date(orderDate) asc
Hope you don’t mind Rudy, for stealing the date part.
I can’t say I’ve kept up with your recent comments…
But as far as I know there is no performance issue with BETWEEN in MySQL. Then again, I’m not doing much MySQL. So you may be right…
Anyway, what you’re doing is a pre-optimization. Not sure if it’s a scenario fit for all cases. Or for all RDBMSs. Or for all versions of the same RDBMSs.
BETWEEN reads better which is something SQL is intended for. I can agree upon further hints or pre-optimization only on case by case evaluations and after proper testing of each case. Me trying too hard do the job of the RDBMS can also mean me staying in the way of RDBMS doing a proper job. It’s possible MySQL needs that.
No one was questioning performance or SQL, just merely a logic issue.
If you take UNIX_TIMESTAMP of today’s date, it returns 12am this morning (For me today is Monday 4th June). Add 6 days, you get Sunday 10th June - but that morning. So with BETWEEN UNIX_TIMESTAMP(CURRENT_DATE) AND UNIX_TIMESTAMP(CURRENT_DATE + INTERVAL 6 DAY), you would get all orders from this morning to Sunday 12am (on the dot), but nothing else for Sunday. So if you were (for example) grouping it by date later on, it would claim that there was 1 order on Sunday when there could be any amount.
With the +8 days, it returns everything up until 12am next Tuesday, but with < rather than <=, you get everything up until (and including) 11.59pm on Monday night, so that’d return everything from this morning until a week tonight.
Of course, those requirements are up to the OP to decide upon.