Query

[sandy1028]

Hi,

 

How to find the values of exactly one day old data from curtime()

 

Suppose time() is 12:40:56 to find data between time() and previous day 12:40:56.

[redarrow]

read this please

 

mysql> SELECT '1997-12-31 23:59:59' + INTERVAL 1 SECOND;
        -> '1998-01-01 00:00:00'
mysql> SELECT INTERVAL 1 DAY + '1997-12-31';
        -> '1998-01-01'
mysql> SELECT '1998-01-01' - INTERVAL 1 SECOND;
        -> '1997-12-31 23:59:59'
mysql> SELECT DATE_ADD('1997-12-31 23:59:59',
    ->                 INTERVAL 1 SECOND);
        -> '1998-01-01 00:00:00'
mysql> SELECT DATE_ADD('1997-12-31 23:59:59',
    ->                 INTERVAL 1 DAY);
        -> '1998-01-01 23:59:59'
mysql> SELECT DATE_ADD('1997-12-31 23:59:59',
    ->                 INTERVAL '1:1' MINUTE_SECOND);
        -> '1998-01-01 00:01:00'
mysql> SELECT DATE_SUB('1998-01-01 00:00:00',
    ->                 INTERVAL '1 1:1:1' DAY_SECOND);
        -> '1997-12-30 22:58:59'
mysql> SELECT DATE_ADD('1998-01-01 00:00:00',
    ->                 INTERVAL '-1 10' DAY_HOUR);
        -> '1997-12-30 14:00:00'
mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
        -> '1997-12-02'
mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',
    ->            INTERVAL '1.999999' SECOND_MICROSECOND);
        -> '1993-01-01 00:00:01.000001'

 

link

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

 

[sandy1028]

I used the query

 

select * from `table_name` where `fieldname` < now() and `fieldname` > now()-interval 1 day

 

 

But I am not getting exactly 24hours data.

 

I am getting the data from cur timestamp till previous day data till 2007-09-13 00:00:00

 

How to get exactly 24 hours data from current timestamp

[jitesh]

SELECT * FROM TABLE WHERE (DATEDIFF(CURDATE(),`feildname`) = 1);

[sandy1028]

But I want all data to be fetched from todays's curtimestamp to yesterdays same timestamp. So xactly 24hrs data should be fetched.

 

 

The query fetchs data from the timestamo 00:00:00 of yesterdays date

[jitesh]

SELECT * from table WHERE `feildname` between (CURRENT_TIMESTAMP()) AND (CURRENT_TIMESTAMP() - INTERVAL 24 HOUR)