More appropriately, use curdate()feature from within sql. select * where date= < curdate()-1;
for example ~Kelly W. Black -----Original Message----- From: Benjamin Pflugmann [mailto:benjamin-mysql@;pflugmann.de] Sent: Monday, November 04, 2002 1:06 PM To: Jim Hogan Cc: [EMAIL PROTECTED] Subject: Re: mysql DATETIME substraction problem/? Hello. On Sun 2002-11-03 at 10:44:43 -0800, [EMAIL PROTECTED] wrote: > Hello! > > I am working on an analysis that is very much dependent on calculating > time differences in seconds. The most simple example: > > I have 3 variables: time_begin (DATETIME) time_end (DATETIME) and elapsed_ > seconds (INT). The data in these DATETIMES looks fine YYYY-MM-DD HH:MM:SS > as expected and actual differences between time_end and time_begin are > usually on the order of 20-120 seconds. > > I run: UPDATE mytable SET elapsed_seconds=time_end-time_begin; You may not use +- on timestamps (DATETIME or TIMESTAMP) directly. For calculating differences between dates in seconds UNIX_TIMESTAMP() is probably the most useful, i.e. UPDATE mytable SET elapsed_seconds = UNIX_TIMESTAMP(time_end) - UNIX_TIMESTAMP(time_begin); Your method used the numer representation of the dates (e.g. 19971231235959) and did an integer substration, which will not take into account that seconds and minutes wrap at 60 and so on. [...] > http://www.mysql.com/doc/en/Date_and_time_functions.html several times and > did not see a different function of syntax for the UPDATE that I want to > do. That man page (in the section on DATE_SUB) says that as of MySQL 3.23 > I can simply use +/- operators Read that again. It only says that for using INTERVAL, i.e. SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND; is allowed instead of SELECT DATE_ADD("1997-12-31 23:59:59", INTERVAL 1 SECOND); > By way of troubleshooting, I created 6 other elapsed_time variables using > small, medium and big int, and float, double and decimal to see if the > result was different, but the result was the same for each. [...] That's because your problem does not stem from your storage. Btw, you can easily see that by using SELECT time_end-time_begin FROM mytable LIMIT 20; Regards, Benjamin. -- [EMAIL PROTECTED] --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
