活动开始时间和结束时间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 )
)
);