[RESOLVED] Extracting MONTH from Unix Timestamp in DB

Mr_Mako2

Hi...

I've searched through the forums for a resolution to this problem, and haven't had any luck with the examples I've found so far.

What I am needing to do is get the current month from a database field that contains timestamp data in UNIX Timestamp format. I need to construct a query that get's the current month from the timestamp, so I can get the number of rows that have been entered in the current month. The current sql query I'm working with is this :

$mnthchk = date('U');
$mnth = date('F', $mnthchk);

$sql = "SELECT data_id, FROM_UNIXTIME(data_timestamp, '%M') FROM data WHERE data_timestamp = '$mnth'";
$result = @mysql_query($sql, $connection) or die(mysql_error());

$nrows = mysql_num_rows($result);

So far, I've not had any luck. I've tried to encapsulate the FROM_UNIXTIME inside a MONTH (as in MONTH(FROM_UNIXTIME(data_timestamp), '%M') and MONTH(FROM_UNIXTIME(data_timestamp, '%M')) ) without any success. The data_timestamp field is an INT(15) field in the database.

Am I not going about this query construct correctly? Obviously I'm not getting the query correct, but is there perhaps another way to accomplish this?

sneakyimp

if your database field called data_timestamp has a unix timestamp in it, it's going to contain what is likely a really large integer value that represents the number of seconds since 1970. the query you've constructed won't work at all because $mnth is going to contain a text string like 'January'. I would guess that query is never going to return a single row.

If you are trying to select from your table called 'data' all events that correspond to the current month, you could try something like this:

$current_month = date('n');
$sql = "SELECT data_id, DATE_FORMAT(FROM_UNIXTIME(date_timestamp), '%W %M %e %Y') FROM data WHERE MONTH(date_timestamp) = " . $current_month;

Mr_Mako2

Ok... I tried that, and didn't get any rows either. My test, using your example below, was :

$current_month = date('F');

$sql = "SELECT data_id, DATE_FORMAT(FROM_UNIXTIME(date_timestamp), '%M') FROM data WHERE MONTH(date_timestamp) = " . $current_month;

I tried it first with "date('n')" as in your example with no results. So I tried it again like above with "date('F')"... and I still didn't get any results for that either. Any other possibilities?

cahva

How about:

$sql = "SELECT data_id FROM data WHERE MONTH(FROM_UNIXTIME(date_timestamp)) = MONTH(CURRDATE())";

// or

$month = date('n');
$sql = "SELECT data_id FROM data WHERE MONTH(FROM_UNIXTIME(date_timestamp)) = '$month'";

Mr_Mako2

cahva wrote:

How about:

$sql = "SELECT data_id FROM data WHERE MONTH(FROM_UNIXTIME(date_timestamp)) = MONTH(CURRDATE())";

// or

$month = date('n');
$sql = "SELECT data_id FROM data WHERE MONTH(FROM_UNIXTIME(date_timestamp)) = '$month'";

The first example returns this error code :

FUNCTION data.CURRDATE does not exist

The second example worked great... thanks! 🙂

cahva

Mr Mako wrote:
The first example returns this error code :
FUNCTION data.CURRDATE does not exist

Yep.. Its CURDATE() 🙂