CODEWITHSUNDEEP
phpmysqltimestamp
TheBlackBenzKid
TheBlackBenzKid

Reputation: 27087

How can I return data for the current month in MySQL using timestamp and PHP?

This should address all other data with PHP/MySQL timestamp I have. Basically I use the timestamp feature in MySQL and PHP

I insert using now for MySQL:

now()

or something like this in PHP:

$date=date("Y-m-d H:i:s");

This is some of my data:

Id          AdId    Unique Md5                                              Date                Stamp                                   IP

    61860       207     92cea3a296fdc76bac6fda219xxxxxx     2012-12-01  2012-12-01 17:48:31     74.125.181.xx
    61859       208     abe3aec5c24301db2d5803a310xxxxxx  2012-12-01    2012-12-01 17:48:31     74.125.181.xx
    61858       207     c93c67a013c64df53cbeec9346xxxxxx  2012-12-01    2012-12-01 17:48:11     95.21.xxx.138
    61857       207     596cf87f2405874c7172f6d0dcxxxxxx    2012-12-01  2012-12-01 17:47:54     188.xxx.98.242
    61856       212     bbba41c211e2829d622c55d00dxxxxxx  2012-12-01    2012-12-01 17:47:17     141.xxx.9.40

This is one of my queries. Basically I am trying to get row total for all the AdId with 207 and the timestamp of this month (2012-12)

SELECT COUNT(adid) AS hits FROM `ha1`.`adviews` WHERE `adid`='212' AND `stamp`='2012-12'

It returns 0 results. I was going to do:

<?php
$currentDate=date("Y-m-d H:i:s");
$currentMonth=date("Y-m");

$trafficsql=mysql_query("SELECT COUNT(adid) AS hits FROM `".$dn."`.`adviews` WHERE `adid`='".$rz["id"]."' AND `stamp`='".$currentMonth"'");
while($vss=mysql_fetch_array($trafficsql)){
    echo $vss["hits"];
}

Upvotes: 1

Views: 3287

Answers (4)

bonCodigo
bonCodigo

Reputation: 14361

You could use following logic in php syntax :-)

WHERE MONTH(stamp) = MONTH(CURRENT_DATE) AND YEAR(stamp) = YEAR(CURRENT_DATE)

Upvotes: 1

hd1
hd1

Reputation: 34657

SELECT COUNT(adid) AS hits FROM ha1.adviews WHERE adid=212 AND stamp>date_sub(now(), interval 1 month)

Assuming your stamp column is of type datetime

Upvotes: 0

GBD
GBD

Reputation: 15981

Try

$trafficsql=mysql_query("SELECT COUNT(adid) AS hits FROM `".$dn."`.`adviews` WHERE `adid`='".$rz["id"]."' AND DATE_FORMAT(`stamp`, '%Y-%m')='".$currentMonth"'");

Use: DATE_FORMAT()

Upvotes: 1

TCFDS
TCFDS

Reputation: 622

You can compare timestamp as a string but then you need to use LIKE

SELECT COUNT(adid) AS hits 
FROM `ha1`.`adviews` 
WHERE `adid`='212' AND `stamp` LIKE '2012-12%'

Upvotes: 0

Related Questions