TIMEDIFF()

Hello,

I am trying to construct a query to be run against a timestamp so that
data is displayed from a certain time backwards. For example:

SELECT Time_Stamp, Park_Name, Mill_Name, Size, Email_Type FROM emails
WHERE TIMEDIFF(NOW(),Time_Stamp) <= '96:00:00' ORDER BY Time_Stamp
LIMIT 0, 30

This would theoretically pull all the records from this moment until 4
days back. However, this does not work unless I have an amount of time
(in hours) which is not 2 digits. For example, it works correctly from
0-9. For example:

The query works, but the results are varied and spread out over the
whole of the table.

SELECT Time_Stamp, Park_Name, Mill_Name, Size, Email_Type FROM emails
WHERE TIMEDIFF(NOW(),Time_Stamp) <= '06:00:00' ORDER BY Time_Stamp
LIMIT 0, 30

Works correctly.

Can anyone please guide me in the right direction with this? I have
tried using '96 HOURS' instead of 96:00:00 but the results are still
not correct.

All help appreciated.

Chris de Britain

Re: TIMEDIFF()

chrisdebritain@gmail.com wrote:

> Hello,

> I am trying to construct a query to be run against a timestamp so that
> data is displayed from a certain time backwards. For example:

> SELECT Time_Stamp, Park_Name, Mill_Name, Size, Email_Type FROM emails
> WHERE TIMEDIFF(NOW(),Time_Stamp) <= '96:00:00' ORDER BY Time_Stamp
> LIMIT 0, 30

> This would theoretically pull all the records from this moment until 4
> days back. However, this does not work unless I have an amount of time
> (in hours) which is not 2 digits. For example, it works correctly from
> 0-9. For example:

> The query works, but the results are varied and spread out over the
> whole of the table.

> SELECT Time_Stamp, Park_Name, Mill_Name, Size, Email_Type FROM emails
> WHERE TIMEDIFF(NOW(),Time_Stamp) <= '06:00:00' ORDER BY Time_Stamp
> LIMIT 0, 30

> Works correctly.

> Can anyone please guide me in the right direction with this? I have
> tried using '96 HOURS' instead of 96:00:00 but the results are still
> not correct.

> All help appreciated.

> Chris de Britain


so if I understand your question correctly - you want all records from
midnight 4 days ago until now?

...
where time_stamp >= date_sub(current_date(),interval 4 day);

Re: TIMEDIFF()

No, it would be all records from the previous 96 hours. Your syntax
has given me some ideas though. Please let me know if you have any
others.

Re: TIMEDIFF()

noone wrote:

> chrisdebritain@gmail.com wrote:

>> Hello,

>> I am trying to construct a query to be run against a timestamp so that
>> data is displayed from a certain time backwards. For example:

>> SELECT Time_Stamp, Park_Name, Mill_Name, Size, Email_Type FROM emails
>> WHERE TIMEDIFF(NOW(),Time_Stamp) <= '96:00:00' ORDER BY Time_Stamp
>> LIMIT 0, 30

>> This would theoretically pull all the records from this moment until 4
>> days back. However, this does not work unless I have an amount of time
>> (in hours) which is not 2 digits. For example, it works correctly from
>> 0-9. For example:

>> The query works, but the results are varied and spread out over the
>> whole of the table.

>> SELECT Time_Stamp, Park_Name, Mill_Name, Size, Email_Type FROM emails
>> WHERE TIMEDIFF(NOW(),Time_Stamp) <= '06:00:00' ORDER BY Time_Stamp
>> LIMIT 0, 30

>> Works correctly.

>> Can anyone please guide me in the right direction with this? I have
>> tried using '96 HOURS' instead of 96:00:00 but the results are still
>> not correct.

>> All help appreciated.

>> Chris de Britain


> so if I understand your question correctly - you want all records from
> midnight 4 days ago until now?

> ...
> where time_stamp >= date_sub(current_date(),interval 4 day);


Okay.. so to get the previous 96hrs only:

where time_stamp >= date_sub(current_date(),interval 96 hours);

note: depending on your version, this will be 'hours'(pre 4.1.1) or 'hour'.


mysql> select sysdate(),sysdate() - interval 96 hour;
+---------------------+------------------------------+
| sysdate() | sysdate() - interval 96 hour |
+---------------------+------------------------------+
| 2006-03-15 14:12:59 | 2006-03-11 14:12:59 |
+---------------------+------------------------------+
1 row in set (0.00 sec)

Re: TIMEDIFF()

Thanks for the reply. However it seems using "where time_stamp >=
date_sub(current_date(),interval 96 hours); " gets a whole 4 days,
starting from midnight, rather than 96 hours.

Also select sysdate(),sysdate() - interval 96 hour; does indeed pull
96 hours, but I am getting errors when I try something like select
sysdate(),sysdate() - interval 96 hour;

where time_stamp >= timesub(sysdate(),interval 96 hours);

This again is my original problem.

Re: TIMEDIFF()

chrisdebritain@gmail.com wrote:
> Thanks for the reply. However it seems using "where time_stamp >=
> date_sub(current_date(),interval 96 hours); " gets a whole 4 days,
> starting from midnight, rather than 96 hours.
>
> Also select sysdate(),sysdate() - interval 96 hour; does indeed pull
> 96 hours, but I am getting errors when I try something like select
> sysdate(),sysdate() - interval 96 hour;
>
> where time_stamp >= timesub(sysdate(),interval 96 hours);
>
> This again is my original problem.
>


try (remember some versions user hour and post-4.1.1 uses hours)

where time_stamp >= sysdate()-interval 96 hours;

as you can see in this example only 1 date is actually >=
sysdate()-interval 96 hours;


mysql> create table d (a int, b timestamp);
Query OK, 0 rows affected (1.31 sec)

mysql> insert into d values (1, sysdate()-interval 96 hour);
Query OK, 1 row affected (0.04 sec)

mysql> insert into d values (1, sysdate()-interval 97 hour);
Query OK, 1 row affected (0.01 sec)

mysql> insert into d values (1, sysdate()-interval 67 hour);
Query OK, 1 row affected (0.01 sec)

mysql> insert into d values (1, sysdate()-interval 98 hour);
Query OK, 1 row affected (0.00 sec)

mysql> select * from d;
+------+----------------+
| a | b |
+------+----------------+
| 1 | 20060313165232 |
| 1 | 20060313155236 |
| 1 | 20060314215241 |
| 1 | 20060313145251 |
+------+----------------+
4 rows in set (0.01 sec)

mysql> select * from d where b >= sysdate()-interval 96 hour;
+------+----------------+
| a | b |
+------+----------------+
| 1 | 20060314215241 |
+------+----------------+
1 row in set (0.03 sec)

by the time I executed the select with the where-clause time had
advanced and so only 1 record matched the criteria.

Re: TIMEDIFF()

Thank you! This seems to work.

noone schrieb:

> chrisdebritain@gmail.com wrote:
> > Thanks for the reply. However it seems using "where time_stamp >=
> > date_sub(current_date(),interval 96 hours); " gets a whole 4 days,
> > starting from midnight, rather than 96 hours.
> >
> > Also select sysdate(),sysdate() - interval 96 hour; does indeed pull
> > 96 hours, but I am getting errors when I try something like select
> > sysdate(),sysdate() - interval 96 hour;
> >
> > where time_stamp >= timesub(sysdate(),interval 96 hours);
> >
> > This again is my original problem.
> >
>
>
> try (remember some versions user hour and post-4.1.1 uses hours)
>
> where time_stamp >= sysdate()-interval 96 hours;
>
> as you can see in this example only 1 date is actually >=
> sysdate()-interval 96 hours;
>
>
> mysql> create table d (a int, b timestamp);
> Query OK, 0 rows affected (1.31 sec)
>
> mysql> insert into d values (1, sysdate()-interval 96 hour);
> Query OK, 1 row affected (0.04 sec)
>
> mysql> insert into d values (1, sysdate()-interval 97 hour);
> Query OK, 1 row affected (0.01 sec)
>
> mysql> insert into d values (1, sysdate()-interval 67 hour);
> Query OK, 1 row affected (0.01 sec)
>
> mysql> insert into d values (1, sysdate()-interval 98 hour);
> Query OK, 1 row affected (0.00 sec)
>
> mysql> select * from d;
> +------+----------------+
> | a | b |
> +------+----------------+
> | 1 | 20060313165232 |
> | 1 | 20060313155236 |
> | 1 | 20060314215241 |
> | 1 | 20060313145251 |
> +------+----------------+
> 4 rows in set (0.01 sec)
>
> mysql> select * from d where b >= sysdate()-interval 96 hour;
> +------+----------------+
> | a | b |
> +------+----------------+
> | 1 | 20060314215241 |
> +------+----------------+
> 1 row in set (0.03 sec)
>
> by the time I executed the select with the where-clause time had
> advanced and so only 1 record matched the criteria.