Re: problem with date/time column
Erick, Wednesday, September 04, 2002, 10:55:55 AM, you wrote: EP> i have a date/time column. i insert into it using "now()". works fine. EP> now i want a query where data/time is either today or yesterday. EP> how should i do it? Take a look at DATE and TIME functions such as CURDATE(), DATE_SUB(): http://www.mysql.com/doc/en/Date_and_time_functions.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - 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
Re: problem with date/time column
Karthik, > Instead why don't you try > select date_sub(now(), INTERVAL 1 DAY) You suggested this earlier. Are you simply repeating the comment or asking me (cf the original poster) a question? Herewith an assumption that you want to maximise your learning. Obviously I can't answer for Erick. However his statement of requirement is/was "where data/time is either today or yesterday". So let's try to understand some terms/specficiations... Take "yesterday". To me (in British Summer Time TZ), yesterday began at midnight of 3September, and finished 24-hours later (others can argue the seconds!) - and apologies to anyone for whom my 'today' is already their 'yesterday'. Thus in SQL the term "yesterday" becomes a BETWEEN comparison. "Today" could also be considered a BETWEEN, but if all datetimes are in the past, it's not necessary to mention tonight's midnight or even NOW() - but if future dates are included, then BETWEEN it must be. Now let's examine DATE-SUB(). If you'll permit the use of my TZ, here's what it looks like: mysql> select now() as MyTime, date_sub(now(), INTERVAL 1 DAY) as TimeBefore; +-+-+ | MyTime | TimeBefore | +-+-+ | 2002-09-04 12:11:32 | 2002-09-03 12:11:32 | +-+-+ 1 row in set (0.01 sec) As you can see, anything that happened during yesterday morning would NOT be included in such an expression. It WILL pick up anything that happened during the last one day/24-hours - both yesterday's and today's 'hours'! Q: Which is 'right'? A: Depends which suits Erick's requirement! Regards, =dn > - Original Message - > From: "DL Neil" <[EMAIL PROTECTED]> > To: "Erick Papadakis" <[EMAIL PROTECTED]>; "mysql" > <[EMAIL PROTECTED]> > Sent: Wednesday, September 04, 2002 3:09 PM > Subject: Re: problem with date/time column > > > > Hi Erick, > > > > > i have a date/time column. i insert into it using "now()". works fine. > > > now i want a query where data/time is either today or yesterday. > > > how should i do it? > > > > > > Calculate the date required (yesterday and/or today) and then add a time > > component of midnight (all zeroes) to make up a date-time value. Then it > is > > a matter of performing a WHERE calculated_time < column_value. > > > > If you are planning on performing date arithmetic you may want to consider > > moving from date-time format to UNIX format timestamps. > > > > Regards, > > =dn > > > > > > > > - > > 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
Re: problem with date/time column
Instead why don't you try select date_sub(now(), INTERVAL 1 DAY) Karthik. - Original Message - From: "DL Neil" <[EMAIL PROTECTED]> To: "Erick Papadakis" <[EMAIL PROTECTED]>; "mysql" <[EMAIL PROTECTED]> Sent: Wednesday, September 04, 2002 3:09 PM Subject: Re: problem with date/time column > Hi Erick, > > > i have a date/time column. i insert into it using "now()". works fine. > > now i want a query where data/time is either today or yesterday. > > how should i do it? > > > Calculate the date required (yesterday and/or today) and then add a time > component of midnight (all zeroes) to make up a date-time value. Then it is > a matter of performing a WHERE calculated_time < column_value. > > If you are planning on performing date arithmetic you may want to consider > moving from date-time format to UNIX format timestamps. > > Regards, > =dn > > > > - > 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
Re: problem with date/time column
Hi Erick, > i have a date/time column. i insert into it using "now()". works fine. > now i want a query where data/time is either today or yesterday. > how should i do it? Calculate the date required (yesterday and/or today) and then add a time component of midnight (all zeroes) to make up a date-time value. Then it is a matter of performing a WHERE calculated_time < column_value. If you are planning on performing date arithmetic you may want to consider moving from date-time format to UNIX format timestamps. Regards, =dn - 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
Re: problem with date/time column
its select date_sub(now(),INTERVAL 1 DAY) - Original Message - From: "Erick Papadakis" <[EMAIL PROTECTED]> To: "mysql" <[EMAIL PROTECTED]> Sent: Wednesday, September 04, 2002 1:25 PM Subject: problem with date/time column > hi, > > i have a date/time column. i insert into it using "now()". works fine. > now i want a query where data/time is either today or yesterday. > > how should i do it? > > thanks/erick > > __ > Do You Yahoo!? > Yahoo! Finance - Get real-time stock quotes > http://finance.yahoo.com > > - > 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
