MySQL fetch rows only for a given month how to do?

[imperium2335]

Hi,

 

I have a database that has a date column in the typical DATETIME format, is it possible to tell mysql to only return records for April as an example?

 

If so would someone be kind enough to show me an example of how?

 

Much appreciated.

 

Also I have started to switch from datetime to the unix timestamp instead as I find it much easier to convert dates etc, what are your views on this?

[PFMaBiSmAd]

Your would use the mysql MONTH() function in your query (and data is generally specific to a month in a specific year, so you generally would want to test the month and year of the data.)

 

WHERE MONTH(datetime_value) = some_number 

 

Unix timestamps have some problems -

 

1) They must be converted (which is a relatively slow function) to be usable for anything that involves humans and calendars.

 

2) You must take into account the timezone setting when you convert them to a human readable format and the timezone database (php's and mysql's) must be kept up to date (a lot of locations have changed their DST start/end dates lately) if you want correct results.

 

3) They have a 1970/2038 or 1901/2038 range limit, depending on php version and operating system, that makes them unsuitable for things like birthdays and future payment calculations...

 

4) They cannot directly be used in queries involving more than a single calendar item at one time (those checking the year, month, or day of the value) without going through a slow conversion of all the data into a yyyy-mm-dd format, which also means that you cannot set an index on the data to speed up your queries. If you want to do efficient queries that find human calendar items, such as all the records with a specific month or all the records between two dates that do any grouping or aggregate functions based on dates, a unix timestamp is the worst way to store the values.

 

If you store a yyyy-mm-dd value in a database, it will always be that value. The same is not true of a unix timestamp. From the mysql documentation -

 

Note: If you use UNIX_TIMESTAMP() and FROM_UNIXTIME() to convert between TIMESTAMP values and Unix timestamp values, the conversion is lossy because the mapping is not one-to-one in both directions. For example, due to conventions for local time zone changes, it is possible for two UNIX_TIMESTAMP() to map two TIMESTAMP values to the same Unix timestamp value. FROM_UNIXTIME() will map that value back to only one of the original TIMESTAMP values

 

You can retrieve a DATETIME or DATE value in any format you want by using the mysql DATE_FORMAT() function in your query. This is at least 8 times faster than using php code with date/strtotime functions to change the format.

 

You can also directly change any formated date/date-time into a DATE or DATETIME value using the mysql STR_TO_DATE() function in a query.

[PFMaBiSmAd]

The people who actively suggest storing unix timestamps in a database are those that have never attempted to efficiently and quickly do things like the following in a query -

SELECT player, pitcher, YEAR(date_recorded), MONTHNAME(date_recorded) as month, SUM(at_bats) as bats FROM your_table WHERE YEAR(date_recorded) BETWEEN 2009 AND 2011 AND player = 'somename' AND pitcher = 'somename' GROUP BY YEAR(date_recorded), MONTH(date_recorded)

 

^^^ Gets the number of times (and any other statistics) a particular player has faced a particular pitcher in each month between 2009 and 2011.

 

To do the above using unix timestamps would require multiple slow conversions from unix timestamps in the query (and no indexes on the data) or multiple queries with the specific unix timestamps for the start/end of each month worth of data.

 

Replace the above query with any other real-life example, such as an inventory report, poll, accounts receivable, event calendar... where you need to operate on information based on more one a single range of date values (a unix timestamp is fine to get data between one unix timestamp and another unix timestamp because you can calculate the unix timestamps that correspond to the start/end of the date range you want.)

[imperium2335]

Thanks for clearing it up for me!