Antal poster indenfor sidste døgn

Jeg er ikke haj i mysql, men i DB2 gør man sådan her:
select count(*) from matchlog where dato > current timestamp - 1 days

Mon ikke det er det samme i mysql

Jeg har aldrig prøvet, men tror dette gør det

select count(*) from matchlog where DATE_ADD(dato,INTERVAL 1 DAY) < now()

læs her http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html

nej - var for hurtig der, den gir ikke inden for 24 timer men ældre end

fra eksemplet:

Here is an example that uses date functions. The following query selects all records with a date_col value from within the last 30 days:


SELECT something FROM tbl_name WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;

Så mon ikke

SELECT count(*) FROM matchlog WHERE DATE_SUB(CURDATE(),INTERVAL 1 DAY) <= dato;

hmm, det er da faktisk det samme

Hmm, det ser ikke ud til at virke helt. Her er 59 poster. Men så vidt jeg kan se burde de 3 første ikke trækkes ud.

CREATE TABLE `matchlog` (
  `dato` datetime NOT NULL default '0000-00-00 00:00:00',
  `userID` int(11) NOT NULL default '0'
) TYPE=MyISAM;

#
# Data dump for tabellen `matchlog`
#

INSERT INTO `matchlog` VALUES ('2004-10-19 19:52:36', 1);
INSERT INTO `matchlog` VALUES ('2004-10-19 19:58:02', 1);
INSERT INTO `matchlog` VALUES ('2004-10-19 21:16:54', 1);
INSERT INTO `matchlog` VALUES ('2004-10-19 22:29:31', 1);
INSERT INTO `matchlog` VALUES ('2004-10-19 22:33:46', 1);
INSERT INTO `matchlog` VALUES ('2004-10-19 22:35:03', 1);
INSERT INTO `matchlog` VALUES ('2004-10-19 23:32:33', 1);
INSERT INTO `matchlog` VALUES ('2004-10-20 09:01:29', 11);
INSERT INTO `matchlog` VALUES ('2004-10-20 09:05:45', 11);
INSERT INTO `matchlog` VALUES ('2004-10-20 09:07:34', 11);
INSERT INTO `matchlog` VALUES ('2004-10-20 09:10:28', 11);
INSERT INTO `matchlog` VALUES ('2004-10-20 10:38:04', 1);
INSERT INTO `matchlog` VALUES ('2004-10-20 10:39:12', 1);
INSERT INTO `matchlog` VALUES ('2004-10-20 10:40:15', 1);
INSERT INTO `matchlog` VALUES ('2004-10-20 10:42:47', 1);
INSERT INTO `matchlog` VALUES ('2004-10-20 11:14:26', 1);
INSERT INTO `matchlog` VALUES ('2004-10-20 11:16:24', 1);
INSERT INTO `matchlog` VALUES ('2004-10-20 11:26:40', 1);
INSERT INTO `matchlog` VALUES ('2004-10-20 11:27:31', 1);
INSERT INTO `matchlog` VALUES ('2004-10-20 11:42:13', 1);
INSERT INTO `matchlog` VALUES ('2004-10-20 11:46:46', 1);
INSERT INTO `matchlog` VALUES ('2004-10-20 11:49:37', 1);
INSERT INTO `matchlog` VALUES ('2004-10-20 11:54:30', 1);
INSERT INTO `matchlog` VALUES ('2004-10-20 11:56:46', 1);
INSERT INTO `matchlog` VALUES ('2004-10-20 11:59:54', 1);
INSERT INTO `matchlog` VALUES ('2004-10-20 12:02:34', 1);
INSERT INTO `matchlog` VALUES ('2004-10-20 12:03:42', 1);
INSERT INTO `matchlog` VALUES ('2004-10-20 12:04:57', 1);
INSERT INTO `matchlog` VALUES ('2004-10-20 12:08:33', 1);
INSERT INTO `matchlog` VALUES ('2004-10-20 12:09:30', 1);
INSERT INTO `matchlog` VALUES ('2004-10-20 12:10:18', 1);
INSERT INTO `matchlog` VALUES ('2004-10-20 12:14:44', 1);
INSERT INTO `matchlog` VALUES ('2004-10-20 12:27:50', 1);
INSERT INTO `matchlog` VALUES ('2004-10-20 12:32:47', 1);
INSERT INTO `matchlog` VALUES ('2004-10-20 12:33:34', 1);
INSERT INTO `matchlog` VALUES ('2004-10-20 12:34:44', 1);
INSERT INTO `matchlog` VALUES ('2004-10-20 12:36:32', 1);
INSERT INTO `matchlog` VALUES ('2004-10-20 12:37:18', 1);
INSERT INTO `matchlog` VALUES ('2004-10-20 12:38:16', 1);
INSERT INTO `matchlog` VALUES ('2004-10-20 12:42:50', 1);
INSERT INTO `matchlog` VALUES ('2004-10-20 12:45:28', 1);
INSERT INTO `matchlog` VALUES ('2004-10-20 13:18:37', 14);
INSERT INTO `matchlog` VALUES ('2004-10-20 13:28:37', 15);
INSERT INTO `matchlog` VALUES ('2004-10-20 13:55:33', 14);
INSERT INTO `matchlog` VALUES ('2004-10-20 14:05:08', 14);
INSERT INTO `matchlog` VALUES ('2004-10-20 14:06:15', 14);
INSERT INTO `matchlog` VALUES ('2004-10-20 14:07:19', 14);
INSERT INTO `matchlog` VALUES ('2004-10-20 14:08:25', 14);
INSERT INTO `matchlog` VALUES ('2004-10-20 14:46:42', 18);
INSERT INTO `matchlog` VALUES ('2004-10-20 15:16:08', 18);
INSERT INTO `matchlog` VALUES ('2004-10-20 15:16:46', 18);
INSERT INTO `matchlog` VALUES ('2004-10-20 15:17:18', 18);
INSERT INTO `matchlog` VALUES ('2004-10-20 15:17:51', 18);
INSERT INTO `matchlog` VALUES ('2004-10-20 17:17:33', 15);
INSERT INTO `matchlog` VALUES ('2004-10-20 20:36:53', 1);
INSERT INTO `matchlog` VALUES ('2004-10-20 20:37:52', 1);
INSERT INTO `matchlog` VALUES ('2004-10-20 20:42:32', 1);
INSERT INTO `matchlog` VALUES ('2004-10-20 20:51:47', 1);
INSERT INTO `matchlog` VALUES ('2004-10-20 20:52:35', 1);

Har lige testet CURDATE() henter hele dage, så det må være denne ( tilpasset dit behov )

SELECT DATE_ADD('1998-01-01 00:00:00', INTERVAL '-1 10' DAY_HOUR);

SELECT count(*) FROM matchlog WHERE DATE_SUB(CURTIME(), INTERVAL '0 24' DAY_HOUR) <= dato

Hej igen,

Jeg løste den med:

            $now = mktime();
            $numMatchesPlayed = 0;
            $query = mysql_query("SELECT UNIX_TIMESTAMP(dato) as dato FROM matchLog WHERE userID='$_POST[userID]'") or die(handleError($PHP_SELF, mysql_error()));
           
            while ($row = mysql_fetch_array($query)) {   
       
                $timeDifference = ($row[dato]-$now);
                if ($timeDifference < 86400){
                   
                    $numMatchesPlayed++;
                }
           
            }

lukker igen

bojo - vi postede på samme tid. Men jeg synes stadig ikke at din virker...

næææ resultatet af DATE_SUB( CURTIME( ) , INTERVAL '0 24' DAY_HOUR ) 
giver 2022-10-20 00:00:00 hos mig, hmmm - den må der leges med. fint du fik løst det

Jeg ved godt spørgsmålet er lukket, men smider af ligevel en SQL-løsning:

SELECT * FROM matchLog WHERE dato >= DATE_SUB(NOW(),INTERVAL 1 DAY) AND dato <= NOW()

Rent performance mæssigt kan det bedre betale sig at lave det i SQL, i stedet for at skulle hente al dataen ud og forarbejde i det i PHP'en.

ahhh, kunne sq heller ikke få det til at passe, men hvorfor f* skriverde mon ikke det i eksemplerne på http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html

hypotetisk spg. det ved du jo ikke :-)