CODEWITHSUNDEEP
mysqlsql
einstein
einstein

Reputation: 13850

IF statement inside where clause in SQL

I'm developing a calendar app. It stores times in two formats. The table have following columns title, startDate, startTime. If the user provides start time plus start date. The database stores UNIX time stamp(number of seconds since UNIX epok) in the column startTime, while startDate is NULL. If the user only provide a start date, the database stores the date in format nnnn-mm-dd in startDate and NULL in ´startTime`. I have this DB structure, because it's easier to display times around the world, because different timezones have different daylight saving times.

I want select the events that are occurring after a specified date. The client computer provide the server with a UNIX timestamp of the beginning of that day($unix) and a date($date) in the format nnnn-mm-dd to select the correct dates.

The problem is, I don't know how to select those days that are occurring as specified above. This solution is not applicable for me, even though it works:

SELECT *
  FROM events
 WHERE startDate >= '$date'
   OR startTime >= '$unix'

The thing is I have in some rows where unix time stamp is provided in startTime, I also have a date provided in startDate and other reason I which I don't want to explain. And because of that I can't use the solution above.

I need some kind of solution that have an IF statement inside the Where clause like:

SELECT *
  FROM events
 WHERE IF(startTime = NULL, startDate >= '$date', startTime >= '$unix')

I'm just guessing this solution. But is it right?

Upvotes: 7

Views: 39620

Answers (6)

josebailo
josebailo

Reputation: 138

I know this is an old question but I think this is the best way to proceed...

SELECT *
FROM events
WHERE IF(startTime IS NULL, '$date', '$unix') =< startDate

Upvotes: 0

M.R.
M.R.

Reputation: 4827

I'm assuming that you want to use startDate when startDate is not null, and startTime when startTime is not null... then try this...


SELECT * 
FROM events
WHERE (startTime is not NULL and startDate >= '$date') 
OR (startTime is not NULL and startTime >= '$unix')

You can use the CASE statement also, but this makes more sense is more readable.

Upvotes: 0

Ibu
Ibu

Reputation: 43810

SELECT * 
FROM events 
WHERE 
  (startDate >= '$date' OR startTime >= '$unix')
AND 
  startDate != NULL

This will not return any row that has null value for startDate

Upvotes: 0

Ken Downs
Ken Downs

Reputation: 4827

All SQL dialects support CASE WHEN:

SELECT *
 FROM events
WHERE CASE WHEN startTime is null
           THEN startDate >= '$date'
           ELSE startTime >= '$unix'

Upvotes: 7

Mitch Wheat
Mitch Wheat

Reputation: 300539

You just need some combined boolean logic in the WHERE clause:

SELECT * 
FROM events
WHERE 
    (startDate IS NULL AND startTime >= '$unix') OR
    (startTime IS NULL AND startDate >= '$date')

Upvotes: 0

zerkms
zerkms

Reputation: 254916

WHERE (startTime IS NULL AND startDate >= '$date')
   OR (startTime IS NOT NULL AND startTime >= '$unix')

Upvotes: 7

Related Questions