As the title indicates, I'm trying to return records where the datetime difference between two datetime columns is greater than X amount of time. I'm having trouble wrapping my head around this concept. I know how to do where column1 is greater than column2, but not calculate the difference between columns and then return results that are greater than or less than a specific amount.

I have built the schema here: http://sqlfiddle.com/#!9/1effe

Can I get some help on this? Basically I'm trying to get records from this table where the difference between publish_date and submission_date is greater than X amount of time, like 1 hour for example. Can I get a hand with this please? I don't even know where to begin.

I'm thinking this is what I want but I'm not sure

  timestampdiff(hour, publish_date, submission_date) > 1
  • 1
    Your syntax is right, you can also use timestampdiff(minute, publish_date, submission_date) BETWEEN 60 AND XXXX. – oNare Jul 31 '15 at 16:39
  • You should add an index on publish_date,submission_date. – oNare Jul 31 '15 at 16:43
  • What do you mean add an index? – robotsushi Jul 31 '15 at 17:24
  • ALTER TABLE test_table ADD INDEX(publish_date, submission_date). (But I doubt if it will help in this case.) – Rick James Jul 31 '15 at 22:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.