ansaurus

Question

Answer 1

A:

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).

amphetamachine 2010-06-13 15:15:36

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)?

sebastian 2010-06-13 16:00:27

Just declare it as a `datetime` and use the `day([colname])` function in your select statements.

amphetamachine 2010-06-13 16:12:21

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.

sebastian 2010-06-13 16:22:11

that might not really work out. What if a users picks the 31st. Will you than only send him a notification every second month?

Kau-Boy 2010-06-13 22:11:33

Answer 2

+1 A:

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.

Kau-Boy 2010-06-13 15:19:54

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)

sebastian 2010-06-13 16:00:50

why not use a tinyint column?

Kau-Boy 2010-06-13 19:22:00

i don't know how can i make this. i guessed DATE(2) would work.

sebastian 2010-06-13 20:58:02

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)).

Kau-Boy 2010-06-13 22:09:16

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

sebastian 2010-06-13 22:26:58

You have to add 1 month to your database value, not to NOW() I think. And what are you comparing the date to? For me it seems that you compare it to the table itself. And you shouldn't use single quotes as they might interprent the value as a string and not a column. Use these quotes instead: `´

Kau-Boy 2010-06-14 09:31:08

ansaurus

tags:

views:

answers:

how to compare dates in php and sql?

related questions