current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I am trying to get the first and last record from a month. In phpmyadmin I ge the correct result. In python I don't. In fact python returns the same row for both of these queries. First number is the table primary key for the rows.

SELECT * 
FROM mcs 
WHERE time_stamp < DATE_ADD( DATE_ADD( LAST_DAY( NOW( ) ) , 
                                       INTERVAL 25 HOUR ) , 
                             INTERVAL -2 MONTH) 
  AND wot_id = 1010965979
LIMIT 1

838831, '1010965979', datetime.datetime(2015, 3, 6, 22, 0), Decimal('444.88'), Decimal('0.00')

SELECT * 
FROM mcs 
WHERE time_stamp < DATE_ADD( DATE_ADD( LAST_DAY( NOW( ) ) , 
                                       INTERVAL 24 HOUR ) , 
                             INTERVAL -1 MONTH)
  AND wot_id = 1010965979 
LIMIT 1

838831, '1010965979', datetime.datetime(2015, 3, 6, 22, 0), Decimal('444.88'), Decimal('0.00')

phpmyadmin shows this row for the first query,

839037 1010965979 2015-04-01 00:00:00 437.36 0.00

and this for the second:

953868 1010965979 2015-04-30 23:00:00 479.67 0.00

using MySQLdb and python 3

What is wrong? is this a bug in MySQLdb? I would not think so as it just passing a SQL string to mysql

share|improve this question

closed as off-topic by Paul White♦, dezso, Phil, Colin 't Hart, RLF May 9 at 21:43

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Paul White, dezso, Phil, Colin 't Hart, RLF
If this question can be reworded to fit the rules in the help center, please edit the question.
1  
LIMIT without ORDER BY does not guarantee consistent results. –  ypercube May 9 at 6:14
    
And that should be 24 HOUR, not 25 ! –  ypercube May 9 at 6:24
    
I was using 25 because I am using < , so thje 25 hours made it 0:0 hours on first day of the month, so less thaan that included last hour of last day of last month. Does that make sense? My time_stamp is limiteds to hours, all min and sec are 0. –  Rob May 9 at 9:49

2 Answers 2

up vote 2 down vote accepted

First, a crucial mistake of the queries:

  • LIMIT without ORDER BY does not guarantee consistent results.

  • If you get correct results using LIMIT without ORDER BY, it's only by accident.

The results you got from running the queries in PHPMyAdmin was only accidental. There is no guarantee that you'll get the same results if you run the same queries tomorrow.

Adding ORDER BY (either ASC or DESC) will solve the issue.

There is also another problem with the 1st query. Adding 25 hours instead of 24 might be a nice hack - if it worked. It might work with the current data but it will stop, if there are ever rows that are not limited to full hours or there are no rows for the first hour of a month. You need to use 24 hours (or 1 day) and timestamp >= ... and ORDER BY .. ASC to get the first row from a period.

Queries rewritten (I used date arithmetic instead of your DATE_ADD(), this is entirely personal preference, the DATE_ADD() is fine in your queries):

-- First row in previous month (or later)
SELECT * 
FROM mcs 
WHERE time_stamp >= LAST_DAY(NOW()) + INTERVAL 1 DAY + INTERVAL -2 MONTH 
  AND wot_id = 1010965979
ORDER BY time_stamp ASC
LIMIT 1 ;

-- Last row in previous month (or earlier)
SELECT * 
FROM mcs 
WHERE time_stamp <  LAST_DAY(NOW()) + INTERVAL 1 DAY + INTERVAL -1 MONTH 
  AND wot_id = 1010965979
ORDER BY time_stamp DESC 
LIMIT 1 ;
share|improve this answer
    
ticked this as answer as it explains why my "stumbled on this solution" actually works. –  Rob May 10 at 7:54
up vote 0 down vote

Solved it by adding

ORDER BY time_stamp DESC LIMIT 1

to the SQL. Must have been a silent 'order by' inserted by PHPmyadmin?

share|improve this answer
    
Adding ORDER BY is correct, you should never have LIMIT without ORDER BY, unless you don't mind inconsistent result. About the explanation, you are wrong. PHPMyAdmin does not add an order by, the results you show there, were only by accident what you wanted. –  ypercube May 9 at 6:23

Not the answer you're looking for? Browse other questions tagged or ask your own question.