数据库 - mysql 计算某个时间，多少天后，多少个月后时间戳

Question

id	start_time	period_ytpe	period_value
1	1461427200	day	3
2	1461427200	month	2

如上表，
start_time 表示开始时间，
period_ytpe 表示期限类型，
period_value表示期限值，

第一第记录表是 3天，第二条表示2个月

如果查询出，从start_time开始，期限在今天之前的记录

比如：
第一条，start_time开始， 三天后的时间戳，如果是在今天（2016-06-04）之前，则是满足条件！
第二条，start_time开始， 两个月后的时间戳，如果是在今天（2016-06-04）之前，则是满足条件！

____________________________________分割线_________________________________________

感谢各位的回答，在不改数据库的情况下（数据表已经被很多地方引用，且不是我设计），我自己写了一条SQL，做了几步转换，感觉性能不会好，但好像可以用了，还好这个表数据量不大，每天最多新增一条新记录。

把开始start_time转为date，再DATE_ADD加上 日或者月，得到时间再转为 unix_time
用今天凌晨 UNIX_TIMESTAMP(CURDATE())减去 上面得到的时候，如果结果大于 0，那就是想要的结果

SELECT `id`,`name`,
CASE `period_type` 
    WHEN 'day' 
        THEN UNIX_TIMESTAMP(CURDATE())-UNIX_TIMESTAMP(DATE_ADD(FROM_UNIXTIME(`start_time`),INTERVAL period_value day))
    WHEN 'month' 
        THEN UNIX_TIMESTAMP(CURDATE())-UNIX_TIMESTAMP(DATE_ADD(FROM_UNIXTIME(`start_time`),INTERVAL period_value month))
    ELSE '' 
END AS 'time_interval'
FROM `table`
WHERE `xxxxxxx'
HAVING time_interval>0
ORDER BY time_interval desc

Answer 1

不知道你用的啥語言。
用最笨的辦法~~
先用你的語言取得到今天的時間戳記。例如1465056000

SELECT * FROM `table` WHERE `period_ytpe` = 'day' AND `start_time` + 86400*`period_value` < 1465056000
UNION
SELECT * FROM `table` WHERE `period_ytpe` = 'month' AND `start_time` + 86400*30*`period_value` < 1465056000

當然缺陷就是，一個月默認當他30天了。
如果你是要用mysql去計算。 。那把start_time存成時間類型吧。 。然後用date_add去計算

AND話外題。 。為啥設計表的時候不直接算好end_time呢？非要篩選的時候去用，計算類別的會導致索引失效，很不好。各種掃表

Answer 2

時間儲存建議將欄位設為timestamp 或datetime 類型, 而不是數值類型的時間戳記.
首先數值類型的時間戳不便於SQL 中查詢, 另程式碼中也需要轉換
在數值類型SQL 中相關函數也無法使用, 需要先對數值類型時間戳轉換城timestamp 或datetime 類型在計算

如果是 timestamp 或是 datetime 可以使用 date_add 函數增加或減少時間達到時間的要求

http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-add

例如

select date_add(start_time, interval 1 day) from t

為 start_time 增加一天

Answer 3

明確問題，你應該要求start_time在某天或某月之後的時間戳記。
思路：用目前時間轉換成date格式，將date加上你要取得的某天或某月的數值，然後將結果轉成時間戳
date_add(date,interval n exp)
其中date為date時間格式，如果是時間戳需要轉換，exp可以為
SECOND 秒SECONDS MINUTE 分鐘MINUTES HOUR 時間HOURS DAY 天DAYS MONTH 月MONTHS
YEAR 年YEARS MINUTE_SECOND 分鐘和秒"MINUTESES:SECON"MINUTESES:SECON"MINUT HOUR_MINUTE 小時和分鐘"HOURS:MINUTES"
DAY_HOUR 天和小時"DAYS HOURS" YEAR_MONTH 年和月"YEARS-MONTHS"
HOUR_SECOND 小時, 分鐘， "HOURS:MINUTES,SECONDS" DAY_MINUTE. "DAYS HOURS:MINUTES" DAY_SECOND 天, 小時, 分鐘, 秒"DAYS HOURS:MINUTES:SECONDS"
1.從start_time開始3天後的時間戳記：
MYSQL程式碼如下

select UNIX_TIMESTAMP(date_add(FROM_UNIXTIME(start_time), interval 3 day)) from t

3代表你要加的天數，將T換成你的資料庫的任意表執行即可
2.從start_time開始N月後的時間戳：

select UNIX_TIMESTAMP(date_add(FROM_UNIXTIME(start_time), interval 3 MONTH)) from t

此函數數MYSQL的，其他資料庫也有對應的函數，但沒有被測過。

Answer 4

試著寫了一下，失敗了。
期望的SQL：

SELECT * FROM table
WHERE start_time < UNIX_TIMESTAMP(DATE_SUB(CURRENT_DATE, INTERVAL `period_value` `period_type`));

結果以上SQL語法有誤，
較接近的SQL：

SELECT * FROM table
WHERE start_time < UNIX_TIMESTAMP(DATE_SUB(CURRENT_DATE, INTERVAL `period_value` DAY));

但這就用不上period_type字段了。

其實如@lyt8384 所說，在SQL語句裡運用不適當的運算會讓索引失效的，查詢效率也會隨之下降。
如果有權限改變表格結構的話，可以新增一個end_time字段，end_time(单位:s)=start_time + period_type * period_value，然後對end_time字段做索引，這樣做應該比較好。