检查存储在mysql db中的unix时间戳的月份名称（使用php）
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
无法解决如何执行以下操作的问题 - 我想列出给定月份的所有事件 将日期存储为数据库中的unix时间戳的事件。 在我的SQL查询中，我必须操纵时间戳，以便我可以检查日期是否与我正在比较的月份字符串在同一个月，例如“october”。 最好的方法是什么？ 谢谢 p> div>
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
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.
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);