Cannot get anything to return via SQL Query

Question

I have a table (jobs) that I am trying to query specific jobs in a time range. The hard part that I am hitting is that the job stream runs over the date change (00:00). So when I am putting in the date I want to see the job stream for, and I enter (for example) 2014-02-11, the logic searches for the job name that was started on the day before what was entered (because the job stream runs over the date change), and then get's all the jobs that run in between there and the end job (on the actual date that was entered in). I have written this query:

SELECT job_name, job_start_time, job_end_time 
FROM job WHERE (job_start_time LIKE '2014-02-10%' AND job_name = 'start')
AND (job_end_time LIKE '2014-02-11%' AND job_name = 'end')
ORDER BY job_end_time ASC;

I can do this programatically in java and parse out the extra non-job stream related jobs, but I'd rather hit the database once and just have the data there that is needed. I thought about trying an inner select, but from everything that I have read, #1, they can be costly and #2 are usually more expensive than a single select.

The above query is returning 0 results and I'm at a loss of what I did wrong.

It would help if you could include your schema and some sample data in a sqlfiddle. — eggyal
– eggyal, Commented Mar 5, 2014 at 18:02

Gordon Linoff · Accepted Answer · 2014-03-05 18:02:58Z

1

Don't use like for anything but strings. So, first try this version:

SELECT job_name, job_start_time, job_end_time 
FROM job
WHERE (date(job_start_time) = date('2014-02-10') AND job_name = 'start') or
      (date(job_end_time) = date('2014-02-11') AND job_name = 'end')
ORDER BY job_end_time ASC;

I also changed the logic to or, because the job_name cannot be both start and end in the same record.

answered Mar 5, 2014 at 18:02

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

4 Comments

eggyal Over a year ago

Surely date('2014-02-10') is the same as '2014-02-10'? And date(job_start_time) etc isn't sargable, so surely it'd be better to do something like job_start_time >= '2014-02-10' AND job_start_time < '2014-02-11'?

ResourceReaper Over a year ago

@Gordon, this returns me the start and end jobs which is a great start. I'm messing around with the query to try and get all the jobs that fall between that date range.

Gordon Linoff Over a year ago

@ResourceReaper . . . If you continue to have problems, ask another question. Getting the records in-between is quite different. And, add sample data and desired results to the new question.

ResourceReaper Over a year ago

Oh I'm sorry! I'll ask a secondary question, your solution got me close. I'll fiddle with it for a while and see what I can come up with before just throwing in the towel. Thanks again!

Red Alert · Accepted Answer · 2014-03-05 18:03:16Z

1

The glaring issue is that you have job_name = 'start' AND job_name = 'end', just separated by a few other terms. job_name can never be two things at once, so you'll always get nothing back.

answered Mar 5, 2014 at 18:03

Red Alert

3,8262 gold badges20 silver badges24 bronze badges

Collectives™ on Stack Overflow

Cannot get anything to return via SQL Query

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related