MySQL Help | Off Topic | unofficial empeg BBS

Quick Links: Empeg FAQ | RioCar.Org | Hijack | BigDisk Builder | jEmplode | emphatic
Repairs: Repairs

You are not logged in. [Log In] empegbbs.com » Forums » General Discussion » Off Topic » MySQL Help

Topic Options

#293603 - 08/02/2007 10:30 MySQL Help
Cybjorg addict Registered: 23/12/2002 Posts: 652 Loc: Winston Salem, NC	I'm not a database expert, so I need some help. I have a field in my database called "StartDate" and is formatted YYYY-MM-DD. I need a query that will select rows from the StartDate and for the next 5 days into the future. Here's what I've come up with (and, obviously, it's not working): Code: SELECT Title, Request, StartDate FROM `Requests` WHERE NOW() >= StartDate AND NOW() <= DATE_ADD(StartDate, INTERVAL 5 DAY)
Top

#293604 - 08/02/2007 14:09 Re: MySQL Help [Re: Cybjorg]
wfaulk carpal tunnel Registered: 25/12/2000 Posts: 16706 Loc: Raleigh, NC US	Is the field defined as a date value or a string? Most databases have an internal date data type, and I doubt that they will automatically convert strings to it. Most have a function to do so, though. I believe for MySQL it's STR_TO_DATE. I want to say that others use AS_DATE. _________________________ Bitt Faulk
Top

#293605 - 08/02/2007 16:42 Re: MySQL Help [Re: Cybjorg]
mlord carpal tunnel Registered: 29/08/2000 Posts: 14486 Loc: Canada	Quote: I have a field in my database called "StartDate" and is formatted YYYY-MM-DD. Yeah. Ugh. That's how MythTV also stores dates in mySQL, as strings that look like YYYY-MM-DD. Which of course means it's all in "localtime", rather than "utctime". Just storing the raw result of time() (number of seconds, UTC, since the epoch), is a wayyy better way to do stuff like that. Especially on POSIX systems. I had to write a small external C routine to convert the YYYY-MM-DD string to seconds (actually, it just uses strptime()), and then just compare with (time() + 5 * 24 * 3600) to check for 5-days into the future. But I know next to nothing about SQL, so.. Cheers Edited by mlord (08/02/2007 19:11)
Top

#293606 - 08/02/2007 18:26 Re: MySQL Help [Re: mlord]
andym carpal tunnel Registered: 17/01/2002 Posts: 3995 Loc: Manchester UK	Quote: Just storing the raw result of time() (number of seconds, UTC, since the epoch), is a wayyy better way to do stuff like that. Expecially on POSIX systems. I discovered this the first time I wrote a DB app in VB a long time ago. Why worry about the number of days in February in 2008 when you can use simple integers to increment/decrement your time and dates and search across ranges. _________________________ Cheers, Andy M
Top

#293607 - 08/02/2007 18:29 Re: MySQL Help [Re: andym]
wfaulk carpal tunnel Registered: 25/12/2000 Posts: 16706 Loc: Raleigh, NC US	After Mark's and Andy's posts, I have to reiterate that virtually all databases have an internal date data type, which makes a lot more sense to use than text strings, obviously, or roll-your-own integer solutions. MySQL, for example, has five: DATETIME, DATE, TIMESTAMP, YEAR, and TIME. _________________________ Bitt Faulk
Top

#293608 - 08/02/2007 18:41 Re: MySQL Help [Re: wfaulk]
Cybjorg addict Registered: 23/12/2002 Posts: 652 Loc: Winston Salem, NC	I'm using the standard MySQL DATE format.
Top

#293609 - 08/02/2007 18:58 Re: MySQL Help [Re: Cybjorg]
wfaulk carpal tunnel Registered: 25/12/2000 Posts: 16706 Loc: Raleigh, NC US	I think your problem is probably that the column is a DATE, but NOW() returns a DATETIME. You probably need to CAST() one or the other. _________________________ Bitt Faulk
Top

#293610 - 09/02/2007 04:21 Re: MySQL Help [Re: wfaulk]
Cybjorg addict Registered: 23/12/2002 Posts: 652 Loc: Winston Salem, NC	Ah, good call. I forgot that NOW() returns a date and time stamp, so I changed the code to this: Code: SELECT Title, Request, StartDate FROM `Requests` WHERE CURDATE() >= StartDate AND CURDATE() <= DATE_ADD(StartDate, INTERVAL 5 DAY) This seems to return the desired result. Thanks for the brain jog.
Top

View All Topics