On Wed, 2005-02-16 at 03:45, Jesper Andersson wrote: > Hello again, > > I relly new with databases and writing sql-questions. > > But in my db want I to check what new rows have come the last hour. > > the db-table (table name is SUBSCRIBER) have the following columns as follows: > > ID email created updated > 001 [EMAIL PROTECTED] 20050215131034 20050215133401 > 063 [EMAIL PROTECTED] 20050215141034 20050215141201 > 076 [EMAIL PROTECTED] 20050215134500 20050215134556 > > The data type of the columns are: > ID =VARCHAR(14) > email =VARCHAR(255) > created =VARCHAR(14) > updated =VARCHAR(14) > > Now I would like to make a sql-question that show which new users have come > the last hour, without that I need to edit the question each time I want to > ask. > (please, donr't blame me that the "date columns" (created & updated) not are > in date format, I have not done the DB from the beginning, I am only tries to > solve some problem in it), I don't think it is possible to change these 2 > columns to "DATETIME" format because then will the web program stop working, > but I would be greatfull for a work around solution that solve my problem. > > Thanks Alec, for your quick answer but it dosn't helped me much, it list > every row any way. I have done my question like this (after your suggestion > (or you have to blame me that I'm stupid if it is not correct ;-))). > > select CREATED from SUBSCRIBER where date_sub(now(), interval 1 hour) <= > created; > > What do I wrong?? > > /J. >
The first thing I note is that your 'timestamp' columns are VARCHAR(14) rather than the TIMESTAMP type, which is designed for exactly this sort of thing. To do any type of date/time math on them, they must be converted to some sort of date time type. The value '20050215134500' may look intuitive, but it is quite awkward from a mathematical perspective. In order to do what you are proposing, you would need to convert a value from every single row for your query which gets more and more expensive as the table grows, and an index is of next-to no value. If it was a 'TIMESTAMP' this same value could be indexed easily and compared via the function TIMESTAMPDIFF() See http://dev.mysql.com/doc/mysql/en/datetime.html and http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html - michael dykman > > > > "Jesper" wrote on 15/02/2005 13:15:43: > > > > > Hello, > > > > > > I relly new with databases and writing sql-questions. But in my > > > db want I to check what have new rows have come the last hour. > > > > > > the db have I as follow: > > > > > > ID email created updated 001 > > > [EMAIL PROTECTED] 20050215131034 20050215133401 > > > 063 [EMAIL PROTECTED] 20050215141034 20050215141201 > > > 76 [EMAIL PROTECTED] 20050215134500 20050215134556 > > > > > > Now I would like to make a sql-question that show which new users > > > have come the last hour, without that I need to edit the question > > > each time I want to ask. > > > > select <colums> from <table> where date_sub(now(), interval 1 hour) <= > > created ; > > > > Alec > > > > -- > ___________________________________________________________ > Sign-up for Ads Free at Mail.com > http://promo.mail.com/adsfreejump.htm -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
