3

I want to make a notification system. Shortly.. to compare two dates, the only problem is that i want to compare the months. to see if a month or two have passed from last notification.

i want to use one or two months from an entry in a mysql database.

the client must select when the notification must come, one or two months.

thank you, Sebastian

Improve this question

2 Answers 2

Reset to default
2

Your statement should look like this:

SELECT * FROM table WHERE date_column + INTERVAL 1 MONTH >= NOW()

You add 1 (or 2) month to the date of the column in which the date is stored and compare it to the current time "NOW()".

That works with DATE, DATETIME and TIMESTAMP columns.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

thanks for your help. i have one more question. how can i add to mysql only the day. Can i create a cell and declare it DATE(2)
why not use a tinyint column?
i don't know how can i make this. i guessed DATE(2) would work.
but using a date column for a column that only 31 diffrent values is a waste of memory. Just save the day of a date using the DAY() function (tinyint_day_column = DAY(date_format)).
thanks for the tip. i can't get the comparison to work. the code you've wrote me doesn't work. i tried this: SELECT * FROM ultima_notificare WHERE DATE_ADD(NOW(), INTERVAL 1 month) > 'ultima_notificare' and some other similar methods but i get only two results: 1. empty result set 2. all of my entries
|
0

You can compare months of two datetime columns inside of MySQL.

The only computer-related shortcoming of saying "a month has passed" is that a "month" is an arbitrary unit of time; some have 30 days, others have 31 and one even has 28. I would suggest using the logic of "30 days has passed" because it is based on a set unit of time, a day, which is 86,400 seconds long (with negligible error of course).

Improve this answer

4 Comments

thanks for your help. i have one more question. how can i add to mysql only the day. Can i create a cell and declare it DATE(2)?
Just declare it as a datetime and use the day([colname]) function in your select statements.
i tried something similar but, i want the user to write only the day. i'm trying to make a notification system and the client must enter only the day that he wants to receive the notification, every month or every two months.
that might not really work out. What if a users picks the 31st. Will you than only send him a notification every second month?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.