CODEWITHSUNDEEP
phpmysql
Erdem Ece
Erdem Ece

Reputation: 1775

calculate difference between two date with php time() function in mysql query

I have searched about this but I could not find anything.

I have a article table. when I write new article I write time into mysql table with using php time() function.

my table is

id article time

time data type is INT(UNSIGNED)

my problem is I want to show articles from last week to today or from last month to today.

how my mysql query should be?

normally I do this

SELECT * FROM articles ORDER BY Time DESC

so this gives me data from today to past. how should I do this? I can't come up with a solution. should I use php-mysql together or can I handle this with only mysql? could you give me idea or example please? thanks.

edit:

I changed to datetime as suggested and now I think I have timezone problem

now my ago() function work 2 hours late. 

<?php 
date_default_timezone_set('Europe/Istanbul'); //timezone function 
ago($time)
{
   $periods = array("saniye", "dakka", "saat", "gün", "hafta", "ay", "yıl", "baya");
   $lengths = array("60","60","24","7","4.35","12","10");

   $now = time();

       $difference     = $now - $time;
       $tense         = "önce";

   for($j = 0; $difference >= $lengths[$j] && $j < count($lengths)-1; $j++) {
       $difference /= $lengths[$j];
   }

   $difference = round($difference);

   return "$difference $periods[$j] önce ";
} //ago function 

echo $Date = ago(strtotime($data['WriteTime'])). 'önce';
?>

Upvotes: 1

Views: 1234

Answers (3)

Mike Brant
Mike Brant

Reputation: 71384

Well, you made a beginner mistake in using the unix timestamp integer for storage in your database. You should almost always use a date/datetime field type, because you invariably need to query against those fields which is much easier when not using unix timestamps.

So, convert your field to datetime, use MySQL's NOW() to insert current timestamps into the field when adding rows.

Then look at the MySQL data/time functions to query against thus field to your heart's desire.

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

Upvotes: 1

raidenace
raidenace

Reputation: 12826

First make time a date type field (and give it a meaningful different name like article_date for e.g)

Then use this query:

SELECT  * FROM    articles 
WHERE   article_date BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE()

Upvotes: 1

Kermit
Kermit

Reputation: 34054

Assuming your time column is a Unix timestamp, you can try something like this: (not tested)

SELECT ... 
FROM magic
WHERE `time` BETWEEN DATE_SUB(FROM_UNIXTIME(`time`), INTERVAL 1 WEEK) AND NOW()

For your month, you would use INTERVAL 1 MONTH. Please, convert your column to common data types and don't use reserved words as the column names.

Upvotes: 1

Related Questions