SQL中where子句中的IF语句 [英] IF statement inside where clause in SQL

问题描述

我正在开发日历应用程序.它以两种格式存储时间.该表具有以下列title, startDate, startTime列.如果用户提供开始时间加上开始日期.数据库将UNIX时间戳(自UNIX epok以来的秒数)存储在列startTime中，而startDate为NULL.如果用户仅提供开始日期，则数据库以startDate的nnnn-mm-dd格式和"startTime"的NULL格式存储日期.我具有这种数据库结构，因为它可以更容易地显示世界各地的时间，因为不同的时区具有不同的夏令时.

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.

我想选择在指定日期之后发生的事件.客户端计算机为服务器提供当天开始时间的UNIX时间戳($unix)和日期($date)，格式为nnnn-mm-dd，以选择正确的日期.

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'

问题是在某些行中，startTime中提供了unix时间戳，在startDate中也提供了日期，还有其他我不想解释的原因.因此，我无法使用上面的解决方案.

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.

我需要某种在where子句中具有IF语句的解决方案，例如:

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?

推荐答案

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

这篇关于SQL中where子句中的IF语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！