how to make question that check the last hour
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. 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to make question that check the last hour
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]
Re: how to make question that check the last hour
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 -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]
how to make question that check the last hour
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. Please help! -- ___ Sign-up for Ads Free at Mail.com http://promo.mail.com/adsfreejump.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to make question that check the last hour
Jesper Andersson [EMAIL PROTECTED] 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to make question that check the last hour
Jesper Andersson wrote: 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. Please help! - try using date_format and now(). See http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html - ian -- +---+ | Ian Sales Database Administrator | | | | If your DBA is busy all the time... | | ...he's not doing his job properly | | ebuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
