0

im storing date in mysql database with timestamp in (int) type column.

but how can i get this ( CURRENT ) month current rows only?

What should be the sql query to fetch desired data?

shawn
  • 137
  • 9
  • I'm curious as to why you aren't storing the date as a timestamp column? – John Sep 30 '11 at 19:02
  • you could try something like this:[stackoverflow.com - timestamp-as-int-field-query-performance][1] [1]: http://stackoverflow.com/questions/2738444/timestamp-as-int-field-query-performance – Mark Sep 30 '11 at 19:02
  • 1
    @john - "The timestamp data type is just an incrementing number and does not preserve a date or a time." - http://msdn.microsoft.com/en-us/library/ms182776(v=sql.90).aspx – Tony Sep 30 '11 at 19:14
  • Not in mysql. Exmple:`create table foo (ts timestamp); insert into foo (ts) values ( now() + interval 1 QUARTER); select * from foo;` returns (approx),2012-01-02 10:22:27 – John Oct 02 '11 at 14:25

3 Answers3

3

Calculate the timestamp of the first and last secondof the month and use sqls BETWEEN

ZeissS
  • 11,867
  • 4
  • 35
  • 50
  • 1
    Beware when using `BETWEEN` with dates, it can include dates outside the month you want to select. See one of my answers and the comments: http://stackoverflow.com/questions/6095542/how-to-query-datetime-object-by-date/6096566#6096566 – Tony Sep 30 '11 at 18:59
0

Add the year as well and you can use the date functions:

SELECT * FROM table WHERE YEAR(FROM_UNIXTIME(timestamp_field)) = YEAR(NOW()) AND MONTH(FROM_UNIXTIME(timestamp_field)) = MONTH(NOW())
konsolenfreddy
  • 9,551
  • 1
  • 25
  • 36
0
$begin = strtotime(date('m/01/Y'));
$end   = strtotime(date('m/t/Y'));

$sql = "SELECT * FROM table WHERE date >= $begin AND date <= $end"

This is probably the easiest and fastest solution. Using a SQL function like FROM_UNIXTIME() can work, but it can lead to performance issues down the road.

Chris G.
  • 3,963
  • 2
  • 21
  • 40