how can I get last weeks records with unix timestamp

kc1 · #1 (**permalink**) 11-19-11, 12:27

Hi everyone,

I'm new to mysql. I am trying to get a list of records inserted into my table over the past week. right now all the records have been inserted over the past week.

here is my show create table:

PHP Code:


			


CREATE TABLE `milpro` (

 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,

 `pro` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

 `today` int(11) unsigned DEFAULT NULL,

 PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=119 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Here is my query:

HTML Code:

SELECT * FROM `milpro` WHERE (from_unixtime(`today`)) BETWEEN
DATE_SUB( CURDATE( ) ,INTERVAL 7 DAY)  AND CURDATE( )

I am getting a resultset of 0 when I put this into phpmyadmin

Can anyone show me what I am doing wrong?

Thanks in advance,

KC

r937 · #2 (**permalink**) 11-19-11, 17:07

that looks okay to me

could you show the results of this query please --

Code:

SELECT * FROM milpro
ORDER BY today DESC LIMIT 5

kc1 · #3 (**permalink**) 11-19-11, 18:27

id pro today Descending
Edit Delete 104 BIGSPROXS 1321721536
Edit Delete 105 FRESHCONNECTING 1321721536
Edit Delete 103 ALWAYSHIDEN 1321721536
Edit Delete 102 BYPASSLINK 1321721536
Edit Delete 101 BYPASSHYPERLINK 1321721536

Thanks for looking at it , r937!

KC

r937 · #4 (**permalink**) 11-19-11, 18:46

okay, i figured it out

DATE_SUB(CURDATE(),INTERVAL 7 DAY) id 2011-11-12
CURDATE() is 2011-11-19

the value (there was only one unique value) you posted was 1321721536 which equates to 2011-11-19 11:52:16

in other words, it was outside the range, it was greater than 2011-11-19 00:00:00

if you want to include today's rows, you have to do this --

Code:

SELECT * 
  FROM milpro
 WHERE today >= UNIX_TIMESTAMP(CURRENT_DATE - INTERVAL 7 DAY) -- 7 days ago  
   AND today  < UNIX_TIMESTAMP(CURRENT_DATE + INTERVAL 1 DAY) -- less than tonight midnight

please note: the conversion is done on the constant date values, not on the column, so this allows your query to be optimized with an index on the today column

kc1 · #5 (**permalink**) 11-19-11, 21:29

Thank you r937! ( Its working )

Best regards,

KC

Internet Services

Web Development

Digital Marketing

Consumer Tech