Re: how to make question that check the last hour

[Roger Baklund]

Jesper Andersson wrote:
[...]
created =VARCHAR(14)
[...]
select CREATED from SUBSCRIBER where date_sub(now(), interval 1 hour) <= 
created;
What do I wrong??
The 'created' column is a normal string, the date_sub() function returns 
a "time string", i.e. a string on the special format "YYYY-MM-DD 
HH:MM:SS". You must convert one to the format of the other, for instance:

select created
  from SUBSCRIBER
  where date_format(
    date_sub(now(), interval 1 hour),
    '%Y%m%d%H%i%s') <= created
This could be simplified like this with MySQL 3.23 or later:
select created
  from SUBSCRIBER
  where date_format(
    now()-interval 1 hour),'%Y%m%d%H%i%s') <= created
You could also convert the other way:
select created
  from SUBSCRIBER
  where
    date_sub(now(), interval 1 hour) <=
    date_format(created,'%Y-%m-%d %H:%i:%s')
This is however not recommended in this case, because we get a function 
call on the created column which must be executed for each tested row, 
preventing the MySQL server from using an index on this column, if one 
is defined.

date_format(), date_sub(), the 'interval' operator and other date/time 
functions are documented here:

<URL: http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html >
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]