Where条件中的if(){}else{}

 

活动开始时间和结束时间start_time、end_time为时间戳

查询正在进行中的活动，当前时间>=开始时间 并且 当前时间<结束时间（其中start_time为必填项，end_time非必填项，当end_time未设置则默认结束时间为开始时间的第二天凌晨）

MySQL

-- 方法一
SELECT
	* 
FROM
	activity 
WHERE
	1 = 1 
	AND UNIX_TIMESTAMP( ) >= start_time
AND
IF
	(
	end_time != 0,
	UNIX_TIMESTAMP( ) < end_time,
	UNIX_TIMESTAMP( ) < UNIX_TIMESTAMP( DATE_ADD( str_to_date( DATE_FORMAT( FROM_UNIXTIME( start_time ), '%Y-%m-%d' ), '%Y-%m-%d %H:%i:%s' ), INTERVAL 1 DAY ) ) 
	);

-- 方法二
SELECT
	* 
FROM
	activity 
WHERE
	1 = 1 
	AND UNIX_TIMESTAMP( ) >= start_time 
	AND (
	( end_time != 0 AND UNIX_TIMESTAMP( ) < end_time ) 
	OR (
	end_time = 0 
	AND UNIX_TIMESTAMP( ) < UNIX_TIMESTAMP( DATE_ADD( str_to_date( DATE_FORMAT( FROM_UNIXTIME( start_time ), '%Y-%m-%d' ), '%Y-%m-%d %H:%i:%s' ), INTERVAL 1 DAY ) ) 
	) 
	);

 

Oracle

-- 方法一
SELECT
	* 
FROM
	activity,
	( SELECT Trunc ( ( SYSDATE - TO_DATE ( '1970-01-01 8', 'YYYY-MM-DDHH24' ) ) * ( 60 * 60 * 24 ) ) nowTime FROM DUAL ) t 
WHERE
	1 = 1 
	AND t.nowTime >= start_time 
	AND (
	( end_time != 0 AND t.nowTime < end_time ) 
	OR (
	end_time = 0 
	AND t.nowTime < (
	Trunc ( ( start_time / ( 60 * 60 * 24 ) + TO_DATE ( '1970-01-01 8', 'YYYY-MM-DDHH24' ) ) + 1 ) - TO_DATE ( '1970-01-01 8', 'YYYY-MM-DD HH24' ) 
	) * ( 60 * 60 * 24 ) 
	) 
	);