0

I have a MySQL question. I have a table with three columns: 'salesdate' (contains a date), 'salestime' (contains time) and 'remaining' (contains integer). The table contains both past and future dates. I want to select a value from 'remaining' which corresponds most closely to the current date and time.

I have built the query below but it only selects values where both the date and time values are both true. This could produce the wrong result as there could be rows with yesterday's date in the 'salesdate', but all the 'salestime' values are greater than the current time - so my current query would ignore these...even if they are the closest to the current date/time.

My current query:

$result = mysql_query(
"SELECT remaining 
FROM quantity_time 
WHERE salesdate<=CURDATE() 
AND salestime<=CURTIME() 
ORDER BY salesdate DESC
LIMIT 1 ");

I think what I need to do is query for dates first and then somehow filter this list to show the date/time that is closest to the current time? but how ? If you can help - I've searched and can't find a clear, step by step way to do this. Your help is much appreciated.

Improve this question

3 Answers 3

Reset to default
1

that is closest to the current time?

The first problem is that your data design is wrong. If the time and the date both refer to the same event then the information should be represented by a single datetime field - not 2 separate ones. As a result any queries you run on this data will be very inefficient.

Also, it's not clear from your description whether the salesdate+time might be in the future.

So using your current schema:

SELECT remaining 
FROM quantity_time  
ORDER BY ABS(NOW() - timestamp(salesdate,salestime)) DESC
LIMIT 1

The term 'remaining' implies that you are using this to determine stock levels - which means that your schema is very wrong. The right way to represent stock and transactions is via 2 separate entities (tables).

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Hi. thanks for your analysis. The time and date do refer to the same event. And salesdate+time include future dates (as per my description "The table contains both past and future dates"). The labels may be a bit confusing as this is not a stock/transaction table. I ran the query as above, and this returned the oldest record, but when changed to ASC it returned the last record for today - but it was a future time. Any way to not include future times? If I used datetime field, then how would I do the query?
Changing the date and time field to become one datetime field as you suggested, and using the following query seems to return the correct value: $result = mysql_query( "SELECT remaining FROM quantity_time WHERE datetime<=NOW() Limit 1 ");
Sorry about the ASC/DESC thing - it's late here. To omit future stuff add a WHERE TIMESTAMP(saelesdate,salestime)<NOW(), and remove the ABS fn (but keep it's argument). If performance is important, add an index on your new field.
0

What do you means as most closely to the current datetime?

Put some range, I don't know how accurate it should be, but:

SELECT remaining 
FROM quantity_time 
WHERE salesdate<=CURDATE() 
AND salestime BETWEEN date_sub(NOW(), INTERVAL 24 HOUR) AND date_add(NOW(), INTERVAL 24 HOUR)
ORDER BY salesdate DESC
LIMIT 1
Improve this answer

1 Comment

By closest date/time I mean any row NOT in the future. I want to find the row that is in the past, but closest to the current date/time.
0 
SELECT remaining FROM (
SELECT remaining,
(UNIX_TIMESTAMP(salesdate) - UNIX_TIMESTAMP()) AS difference
FROM quantity_time 
WHERE salesdate<=CURDATE() 
AND salestime<=CURTIME()
) subquery
ORDER BY difference DESC
LIMIT 1

I don't know if this is very performant, but this should at least work. It first makes a subquery where it calculates the time difference in seconds and then it is ordered by the difference.

Improve this answer

3 Comments

this looks like it is doing a timestamp on 'remaining' - which is an integer value. Was this intended?
@user2288451 It doesn't change the remaining column. It only uses a timestamp for the temporary comparison. Execute the query ;)
executed and no this doesn't work. 'remaining' is not a time value. It is a quantity I want returned...so i'm not sure where you are headed with this.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.