Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2011
    Posts
    13

    Unanswered: how can I get last weeks records with unix timestamp

    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` (
     `
    idint(11unsigned NOT NULL AUTO_INCREMENT,
     `
    provarchar(255COLLATE utf8_unicode_ci DEFAULT NULL,
     `
    todayint(11unsigned 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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that looks okay to me

    could you show the results of this query please --
    Code:
    SELECT * FROM milpro
    ORDER BY today DESC LIMIT 5
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2011
    Posts
    13
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2011
    Posts
    13
    Thank you r937! ( Its working )

    Best regards,

    KC

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •