数据库 - 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

I don’t know what language you use.
Use the stupidest way~~
First get today’s timestamp in your language. For example 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

Of course the drawback is that by default, you will be him for 30 days a month.
If you want to use mysql to calculate. . Then save start_time as a time type. . Then use date_add to calculate

AND digression. . Why don't you just calculate end_time when designing the table? If you have to use it when filtering, calculation type will cause index failure, which is very bad. Various meter scanning

Answer 2

For time storage, it is recommended to set the field to timestamp or datetime type instead of numeric type timestamp.
First of all, numeric type timestamp is not convenient to query in SQL, and it also needs to be converted in the code
In numeric value The related functions in type SQL cannot be used. You need to convert the numerical type timestamp to timestamp or datetime type before calculation

If it is timestamp or datetime, you can use the date_add function to increase or decrease the time to meet the time requirements

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

For example

select date_add(start_time, interval 1 day) from t

Add one day to start_time

Answer 3

To clarify the question, you should require start_time to be a timestamp after a certain day or month.
Idea: Convert the current time into date format, add the date to the value of the day or month you want to obtain, and then convert the result into a timestamp
date_add(date,interval n exp)
Where date is the date time format, if it is a timestamp that needs to be converted, exp can be
SECOND seconds SECONDS MINUTE minutes MINUTES HOUR time HOURS DAY days DAYS MONTH months MONTHS
YEAR year YEARS MINUTE_SECOND minutes and seconds "MINUTES:SECONDS" HOUR_MINUTE hours and minutes "HOURS:MINUTES"
DAY_HOUR days and hours "DAYS HOURS" YEAR_MONTH years and months "YEARS-MONTHS"
HOUR_SECOND hours, minutes, "HOURS:MINUTES:SECONDS" DAY_MINUTE days, hours, minutes "DAYS HOURS:MINUTES" DAY_SECOND days, hours, minutes, seconds "DAYS HOURS:MINUTES:SECONDS"
1. Timestamp 3 days after start_time:
MYSQL code is as follows

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

3 represents the number of days you want to add, just replace T with any table in your database and execute
2. The timestamp N months from start_time:

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

This function is for MYSQL. Other databases also have corresponding functions, but they have not been tested.

Answer 4

I tried writing it but failed.
Expected SQL:

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

As a result, the above SQL syntax is incorrect,
A closer SQL:

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

But the period_type field is not needed.

In fact, as @lyt8384 said, using inappropriate operations in SQL statements will invalidate the index and query efficiency will also decrease accordingly.
If you have permission to change the table structure, you can add a end_time field, end_time(单位:s)=start_time + period_type * period_value, and then index the end_time field. This should be better.