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
3
-
What forms can the month string have? Just october? Or also oct, ...?Pekka– Pekka2010-08-27 10:27:33 +00:00Commented Aug 27, 2010 at 10:27
-
"october" does this mean "october 2010" or should the result include events from october 2009, 2011, 1999 as well?VolkerK– VolkerK2010-08-27 10:27:47 +00:00Commented Aug 27, 2010 at 10:27
-
the format of the month can be "october" or "oct" - i can set it to either, the query will also check that the event date is in the future, so i just planned to use the month alone for the comparison, as there won't be a case where an event is more than a year in advanceted776– ted7762010-08-27 10:30:52 +00:00Commented Aug 27, 2010 at 10:30
Add a comment
|
3 Answers
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
Comments
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);
2 Comments
Lekensteyn
MONTHNAME is locale-dependent as of version 5.1.12.
Mike
@Lekensteyn: And so, I presume, would be the string representations of the months that were specified in the question. :-)
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.
Comments
