CODEWITHSUNDEEP
phpmysqltime
Ivijan Stefan Stipić
Ivijan Stefan Stipić

Reputation: 6668

MySQL: Check one month or one year in future inside query

I have one auto-billing process what I need to automate via cron job. I want to made query where I can check in database is payment ready for autobilling or no.

Here is table:

id  user_id     client_id   payment_id                      save_details    auto_billing    price   plan    created     active
3   1           1           PAY-xxxxxxxxxxxxxxxxxxxxxxxx    0               1               10      M       1484576988  2
7   1           1           PAY-yyyyyyyyyyyyyyyyyyyyyyyy    1               1               10      M       1484945652  1
4   1           2           PAY-ssssssssssssssssssssssss    0               1               10      M       1484594351  1
6   1           15          PAY-tttttttttttttttttttttttt    0               1               96      Y       1484615360  1

And my current query:

SELECT
     COUNT(`p`.`auto_billing`) AS `total`
FROM
     `payment` `p`
WHERE
     `p`.`auto_billing` = 1
AND
     `p`.`active` = 1

QUESTION IS:

How I can check if plan is M and add on created +1 month or Y and add on created +1 year to lather I can check via PHP current timestamp and start auto-billing?

I need to somehow transform created field into future expected time of 1 year of month depends on plan variable.

I would like to change ths complete table but system allready have a arround 7000 records and don't want to messup something.

Thanks!

Upvotes: 0

Views: 391

Answers (2)

shukshin.ivan
shukshin.ivan

Reputation: 11340

Just use DATE_ADD and conditional expression:

SELECT UNIX_TIMESTAMP(IF(plan='M', DATE_ADD(FROM_UNIXTIME(created), INTERVAL 1 MONTH), DATE_ADD(FROM_UNIXTIME(created), INTERVAL 1 YEAR))) from payment

This sql will add to created appropriate interval.

Upvotes: 1

O. Jones
O. Jones

Reputation: 108686

Look up FROM_UNIXTIME(). Try queries with WHERE clauses like this one:

      FROM_UNIXTIME(created)  <= NOW() + INTERVAL 1 YEAR

or this one:

      FROM_UNIXTIME(created)  <= NOW() + INTERVAL 1 MONTH

Upvotes: 1

Related Questions