Re: Datediff function
John Meyer wrote: I'm trying to pull up a list of users who haven't tweeted in 7 or more days, and I'm trying to use this statement: SELECT USER_NAME, MAX(TWEET_CREATEDAT) FROM USERS NATURAL JOIN TWEETS WHERE DATEDIFF(NOW(),MAX(TWEET_CREATEDAT)) 7 But it says invalid group function. How should I reword this query? Have you tried this? SELECT USER_NAME , MAX(TWEET_CREATEDATE) as latest_tweet FROM USERS NATURAL JOIN TWEETS GROUP BY USERS.USER_ID HAVING DATEDIFF(NOW(),latest_tweet) 7; OR you could build a distinct list (temporary table) of all users who *have* tweeted in the last 7 days and LEFT JOIN the USERS table to that to figure out who isn't on the list. By moving the evaluation to the HAVING clause (which is evaluated after the GROUP BY) you get to filter on the results of the GROUPing operations. The conditions of the WHERE clause are applied before any GROUPing happens. -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Datediff function
-Original Message- From: Gavin Towey [mailto:gto...@ffn.com] Sent: Wednesday, September 16, 2009 7:34 PM To: John Meyer; mysql@lists.mysql.com Subject: RE: Datediff function Hi John, You can't use aggregate function in the WHERE clause, because they aren't evaluated until after the WHERE clause is applied. [JS] You can use the HAVING clause, although it is probably inefficient. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com Wouldn't it be much easier to simply keep a last_tweet_date field updated somewhere then simply do SELECT USER_NAME FROM USERS WHERE last_tweet_date NOW()-INTERVAL 7 DAY; ? Regards, Gavin Towey -Original Message- From: John Meyer [mailto:johnme...@pueblocomputing.com] Sent: Wednesday, September 16, 2009 12:52 PM To: mysql@lists.mysql.com Subject: Datediff function I'm trying to pull up a list of users who haven't tweeted in 7 or more days, and I'm trying to use this statement: SELECT USER_NAME, MAX(TWEET_CREATEDAT) FROM USERS NATURAL JOIN TWEETS WHERE DATEDIFF(NOW(),MAX(TWEET_CREATEDAT)) 7 GROUP BY USERS.USER_ID But it says invalid group function. How should I reword this query? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the- infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Datediff function
Hi John, If judicious transformation of data makes it easier to do the queries you want, then you should consider it. ETL isn't a common acronym in the database world just because we like three letters =) Though it depends on how often you're doing this, if it's one-off then it's probably not worth it, though I was making the assumption you're probably going to be using that query frequently. Regards, Gavin Towey -Original Message- From: John Meyer [mailto:johnme...@pueblocomputing.com] Sent: Wednesday, September 16, 2009 4:51 PM To: Gavin Towey Cc: mysql@lists.mysql.com Subject: Re: Datediff function Gavin Towey wrote: Hi John, You can't use aggregate function in the WHERE clause, because they aren't evaluated until after the WHERE clause is applied. Wouldn't it be much easier to simply keep a last_tweet_date field updated somewhere then simply do SELECT USER_NAME FROM USERS WHERE last_tweet_date NOW()-INTERVAL 7 DAY; ? Regards, Gavin Towey I don't know if that would be so simple. I'd have to run programming logic when I fetch the information off the twitter server. I just hoped that there was a way to do it through SQL. The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Datediff function
I'm trying to pull up a list of users who haven't tweeted in 7 or more days, and I'm trying to use this statement: SELECT USER_NAME, MAX(TWEET_CREATEDAT) FROM USERS NATURAL JOIN TWEETS WHERE DATEDIFF(NOW(),MAX(TWEET_CREATEDAT)) 7 GROUP BY USERS.USER_ID But it says invalid group function. How should I reword this query? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Datediff function
Hi John, You can't use aggregate function in the WHERE clause, because they aren't evaluated until after the WHERE clause is applied. Wouldn't it be much easier to simply keep a last_tweet_date field updated somewhere then simply do SELECT USER_NAME FROM USERS WHERE last_tweet_date NOW()-INTERVAL 7 DAY; ? Regards, Gavin Towey -Original Message- From: John Meyer [mailto:johnme...@pueblocomputing.com] Sent: Wednesday, September 16, 2009 12:52 PM To: mysql@lists.mysql.com Subject: Datediff function I'm trying to pull up a list of users who haven't tweeted in 7 or more days, and I'm trying to use this statement: SELECT USER_NAME, MAX(TWEET_CREATEDAT) FROM USERS NATURAL JOIN TWEETS WHERE DATEDIFF(NOW(),MAX(TWEET_CREATEDAT)) 7 GROUP BY USERS.USER_ID But it says invalid group function. How should I reword this query? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Datediff function
Gavin Towey wrote: Hi John, You can't use aggregate function in the WHERE clause, because they aren't evaluated until after the WHERE clause is applied. Wouldn't it be much easier to simply keep a last_tweet_date field updated somewhere then simply do SELECT USER_NAME FROM USERS WHERE last_tweet_date NOW()-INTERVAL 7 DAY; ? Regards, Gavin Towey I don't know if that would be so simple. I'd have to run programming logic when I fetch the information off the twitter server. I just hoped that there was a way to do it through SQL. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
DateDiff function in SqlServer ... How do it in MySql ?
I am a user of Microsoft Sql Server and use very much the function Datediff (interval, fecha1, fecha2) to extract differences between two dates, in years, days, months, hours, etc I am a beginner with MySql and i can't see the way to do this function .. How can I extract for example difference of hours between two given dates .. someone help me please ? Thanks in advance
Re: DateDiff function in SqlServer ... How do it in MySql ?
Gabriel Alessandria wrote: I am a user of Microsoft Sql Server and use very much the function Datediff (interval, fecha1, fecha2) to extract differences between two dates, in years, days, months, hours, etc I am a beginner with MySql and i can't see the way to do this function .. How can I extract for example difference of hours between two given dates .. someone help me please ? Thanks in advance Not quite sure what it means to get the difference between two dates in months or hours, but mysql does have a DATEDIFF function: DATEDIFF(expr,expr2) DATEDIFF() returns the number of days between the start date expr and the end date expr2. expr and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation. For more date functions, see the manual: http://www.mysql.com/doc/en/Date_and_time_functions.html Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DateDiff function in SqlServer ... How do it in MySql ?
Michael Stassen said: Gabriel Alessandria wrote: I am a user of Microsoft Sql Server and use very much the function Datediff (interval, fecha1, fecha2) to extract differences between two dates, in years, days, months, hours, etc I am a beginner with MySql and i can't see the way to do this function .. How can I extract for example difference of hours between two given dates .. someone help me please ? Thanks in advance Not quite sure what it means to get the difference between two dates in months or hours, but mysql does have a DATEDIFF function: DATEDIFF(expr,expr2) DATEDIFF() returns the number of days between the start date expr and the end date expr2. expr and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation. For more date functions, see the manual: http://www.mysql.com/doc/en/Date_and_time_functions.html Michael For finergrain accuracy: (UNIX_TIMESTAMP(TIStop)-UNIX_TIMESTAMP(TIStart))/60 This gets difference in minutes. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
