Date Range Issues

Question

I need to check the records made in the last "x" days (30 days example) and cannot make the request work. This is what I use:

  SELECT   CAL_OWNER,
           CAL_TITLE,
           FROM_UNIXTIME (CAL_CREATED, "%m-%d-%y") AS CREATED,
           FROM_UNIXTIME (RANGE_START, "%Y-%m-%d") AS DATE2BESEEN,
           CASE CAL_REFERRAL_TYPE
              WHEN 1 THEN 'NoReferral'
              WHEN 2 THEN 'CareyGuide'
              WHEN 3 THEN 'Education'
              WHEN 4 THEN 'Employment'
              WHEN 5 THEN 'Housing'
              WHEN 6 THEN 'Medical'
              ELSE 'NA'
           END
              AS REFERRALS
    FROM   EGW_CAL
   WHERE   CAL_CREATED BETWEEN (NOW () - '30 day') AND NOW ()
ORDER BY   REFERRALS ASC;

If I comment on the line "WHERE range_start ...", the query runs fine, but it retrieves all the data. However, if I run the full query, this is not an error, but there are no results (I have 4 entries in the cal_created column over the past 3 weeks).

If anyone can help, I would really appreciate it.

Answer 1

Try using INTERVAL and either NOW () or CURDATE () ..

WHERE FROM_UNIXTIME (CAL_CREATED,'%Y-%m-%d') BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE()

curdate is only part of the day

if you want to enable the use of NOW () time

WHERE FROM_UNIXTIME (CAL_CREATED,'%Y-%m-%d') BETWEEN NOW() - INTERVAL 30 DAY AND NOW()

You can also create a new date for use between

WHERE FROM_UNIXTIME (CAL_CREATED,'%Y-%m-%d') BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW()

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

: ,

---

:

SELECT  
    CAL_OWNER,
    CAL_TITLE,
    FROM_UNIXTIME (CAL_CREATED, '%m-%d-%y') AS CREATED_AT,
    FROM_UNIXTIME (RANGE_START, '%Y-%m-%d') AS DATE2BESEEN,
    CASE CAL_REFERRAL_TYPE
        WHEN 1 THEN 'NoReferral'
        WHEN 2 THEN 'CareyGuide'
        WHEN 3 THEN 'Education'
        WHEN 4 THEN 'Employment'
        WHEN 5 THEN 'Housing'
        WHEN 6 THEN 'Medical'
        ELSE 'NA'
    END AS REFERRALS
FROM  EGW_CAL
WHERE FROM_UNIXTIME(CAL_CREATED,'%Y-%m-%d') BETWEEN (NOW() - INTERVAL 30 DAY) AND NOW()
ORDER BY REFERRALS ASC;

Answer 2

• CAL_CREATED - UNIX,
• NOW() MySQL.

.

WHERE CAL_CREATED 
    BETWEEN UNIX_TIMESTAMP(NOW() - INTERVAL 30 DAY) AND UNIX_TIMESTAMP(NOW());

:

WHERE FROM_UNIXTIME(CAL_CREATED) BETWEEN ...

MySQL .

Answer 3

where internval:

WHERE   CAL_CREATED BETWEEN NOW() - interval 30 day AND NOW()

Postgres. MySQL now() . "30 ".

now() , , . , - 30 . - now().