checking month name against unix timestamp stored in mysql db (using php)

Question

Having trouble working out how to do the following - I want to list all the events for a given month, with each event having a date stored as a unix timestamp in the database. In my sql query, I have to manipulate the timestamp so that i can check if the date is in the same month as the month string i'm comparing against, e.g "october". What would the best way to go about this be? Thanks

Answer 1

Selects all columns from table events if the month equals October (10th month).

SELECT * FROM events WHERE MONTH(FROM_UNIXTIME(event_date)) = 10

More time and date related functions can be found in the MySQL Manual

Answer 2

MySQL can transform unix timestamps to/from its own native date/datetime types with the FROM_UNIXTIME() and UNIX_TIMESTAMP(), after which you can apply the standard date/time manipulation functions, such as MONTH(). Full docs on the date/time manipulation functions here.

If you're doing this sort of thing on a large dataset, you might be better off calculating the start/ending timestamps of whichever particular month you're interested in. Eg. If you're interested in March '09, then calculate the unix timestamp for 12:00:00am, March 1st, and 11:59:59pm, March 31st. Then you can do a simple numeric comparison on your timestamp field, which would allow using indexes.

Answer 3

You could use something similar to this:

SELECT * FROM my_table
WHERE LEFT(MONTHNAME(my_date), 3) = LEFT(('AUGUST'), 3);

You may not need the LEFT function if you are supplying the full month name:

SELECT * FROM my_table
WHERE MONTHNAME(my_date) = 'AUGUST';

If your column is case sensitive, you could modify it so that it looks like this:

SELECT * FROM my_table
WHERE UPPER(LEFT(MONTHNAME(my_date), 3)) = LEFT(('AUGUST'), 3);

Additionally, you may need to convert from the Unix timestamp format:

SELECT * FROM my_table
WHERE UPPER(LEFT(FROM_UNIXTIME(MONTHNAME(my_date)), 3)) = LEFT(('AUGUST'), 3);