homepage Welcome to WebmasterWorld Guest from 54.234.139.234
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Ways of searching by relative dates and times in MySQL
Anything similar to the NOW() command?
otem




msg:3345884
 9:50 pm on May 21, 2007 (gmt 0)

I'm using some queries in MySQL where I'm looking at data lying within certain time ranges.

I've learned about NOW(), but are there similar commands for looking up times in the past or future?

Right now I'm using the following structure:

$query=mysql_query("SELECT * FROM table WHERE created>='".date("Y-m-d H:i:s",time()-1800)."'");

Also, if I wanted to look up data that meets one requirement and one and/or the other of the a second requirement, can I use the following structure:

SELECT * FROM table WHERE x='$x' AND (y='$y' ¦¦ z='$z')

It seems to work for me, but I just wanted to make sure I'm not opening room for a bug or performance issues.

Thanks.

 

otem




msg:3345979
 12:17 am on May 22, 2007 (gmt 0)

Playing around, it looks like I can do the follow:

NOW()-1800 would be 30 minutes in the past
CURDATE()-7 would be seven days ago

Is that right? If somebody could confirm this it would be much appreciated. Thanks.

lorax




msg:3345980
 12:25 am on May 22, 2007 (gmt 0)

Well you seem to be headed in the right direction but it really depends upon the data you want to search on. For example. MySQL allows you to narrow the dataset you return or evaluate on a date field by using one of the standard date references (year, month, or day) like so:

SELECT YEAR(thedate) as year
FROM sometable
WHERE YEAR(thedate) < '2010' && YEAR(thedate) > '2000';

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved