RE: Struggling with the logic
Thanks to all for the responses, as always problem now solved and I learnt a few things. Regards John Berman -Original Message- From: Chris Sansom [mailto:[EMAIL PROTECTED] Sent: 23 July 2006 16:02 To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Struggling with the logic At 15:43 +0100 23/7/06, John Berman wrote: I have a table called: submissions and each record has an approvedate field which stores the date mm/dd/ Why? If you're storing the date in this format you can only be storing it as a string (char, varchar or text), so no wonder you're having trouble with it, when MySQL has a perfectly good date storage type in the form -mm-dd. I want to display all records for 7 days only from their approved date Assuming you've changed the way you store your dates: SELECT * FROM submissions WHERE DATE_ADD(approvedate, INTERVAL 7 DAY) = NOW () For what it's worth, the standard American date format of mm/dd/ has always mystified me, as it's the least logical possible way to do it. The SQL format - in decreasing order of unit size - is of course the most logical way because you can guarantee to sort on it and do other calculations. Over here in Europe we at least use dd/mm/ (increasing unit size order), which is the next most logical, but to start with the middle-sized unit, put the smallest unit in the middle and end with the largest is just... weird! -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Marriage has driven more than one man to sex. -- Peter de Vries -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.10.3/395 - Release Date: 21/07/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Struggling with the logic
Hi Chris, John all! Chris' explanation is nearly correct: Chris Sansom wrote: At 15:43 +0100 23/7/06, John Berman wrote: I have a table called: submissions and each record has an approvedate field which stores the date mm/dd/ Why? If you're storing the date in this format [[...]] John, you should ensure to differ between storage format and presentation format(s): - For storage, ensure that it can be sorted on (order year, month, day). Typically, this is achieved by telling the database a proper type (like DATE or DATETIME) and then just supplying a value. - For presentation, use what suits the users' taste. This may be configurable or depending on context (like drop the year if it is obvious etc.), and you can achieve all (?) desired results by the DATE_FORMAT() function. There should be only one storage format, but there may be many different ways to present a date. If you do not use the proper SQL type (DATE, DATETIME, ...), you cannot rely on SQL doing the desired operations (sorting, calculations, ...). [[...]] The SQL format - in decreasing order of unit size - is of course the most logical way because you can guarantee to sort on it and do other calculations. [[...]] That is correct except for the designation SQL: It really is an ISO format. The important property is having the most significant value (year) in the first (leftmost) place. Joerg Bruehe -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Struggling with the logic
At 17:31 -0700 23/7/06, [EMAIL PROTECTED] wrote: The logic is that it follows the natural spoken format, i.e. July 23, 2006; which became the written standard; which... Hmmm. Is 'July the 23rd, 2006' any more natural to say than 'the 23rd of July, 2006'? I think we probably say either, equally. Sorry - I don't buy that. :-) Myself: I've never let local standards stand in the way of my using international ones. Excellent. -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ To see tomorrow's PC, look at today's Macintosh -- Byte 1995 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Struggling with the logic
As mentioned before you really should sue the date data type in mysql,
or alternativly use an int and store the date as seconds since the epox,
so that you can do simple math for this type of query. Any other way of
storing dates is basically shooting yourself in the foot.
That said, if you choose to store it as a varchar use:
Select * from submissions where approvedate in ('07/01/2006',
'07/02/2006', '07/03/2006',...);
Be aware how extremely slow this performs compared to the options
mentioned above tho.
John Berman wrote:
Hi
I'm struggling with some logic
I have a table called: submissions and each record has an approvedate field
which stores the date mm/dd/
I want to display all records for 7 days only from their approved date so I
guess something like
Select * from submissions were approvedate - this is were im getting stuck
Pointers appreciated, im sure its simple ?
Regards
John B
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Struggling with the logic
Hi I'm struggling with some logic I have a table called: submissions and each record has an approvedate field which stores the date mm/dd/ I want to display all records for 7 days only from their approved date so I guess something like Select * from submissions were approvedate - this is were im getting stuck Pointers appreciated, im sure its simple ? Regards John B
Re: Struggling with the logic
At 15:43 +0100 23/7/06, John Berman wrote: I have a table called: submissions and each record has an approvedate field which stores the date mm/dd/ Why? If you're storing the date in this format you can only be storing it as a string (char, varchar or text), so no wonder you're having trouble with it, when MySQL has a perfectly good date storage type in the form -mm-dd. I want to display all records for 7 days only from their approved date Assuming you've changed the way you store your dates: SELECT * FROM submissions WHERE DATE_ADD(approvedate, INTERVAL 7 DAY) = NOW () For what it's worth, the standard American date format of mm/dd/ has always mystified me, as it's the least logical possible way to do it. The SQL format - in decreasing order of unit size - is of course the most logical way because you can guarantee to sort on it and do other calculations. Over here in Europe we at least use dd/mm/ (increasing unit size order), which is the next most logical, but to start with the middle-sized unit, put the smallest unit in the middle and end with the largest is just... weird! -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Marriage has driven more than one man to sex. -- Peter de Vries -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Struggling with the logic
Hello John There are two approaches to this. 1) Best, save date into table as a number. Select date like WHERE date=$date1 AND date=$date2 2)Otherwise, if storing dates a strings: WHERE date=$date1 OR date=$date2 OR date=$date3 OR date=$date4 OR date=$date5. In general, if you want to select date ranges and perform other date calculations, save the date as a unix date number then convert to human readable form as necessary. John Berman wrote: Hi I'm struggling with some logic I have a table called: submissions and each record has an approvedate field which stores the date mm/dd/ I want to display all records for 7 days only from their approved date so I guess something like Select * from submissions were approvedate - this is were im getting stuck Pointers appreciated, im sure its simple ? Regards John B -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Struggling with the logic
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 For what it's worth, the standard American date format of mm/dd/ has always mystified me, as it's the least logical possible way to do it. The SQL format - in decreasing order of unit size - is of course the most logical way because you can guarantee to sort on it and do other calculations. Over here in Europe we at least use dd/mm/ (increasing unit size order), which is the next most logical, but to start with the middle-sized unit, put the smallest unit in the middle and end with the largest is just... weird! The logic is that it follows the natural spoken format, i.e. July 23, 2006; which became the written standard; which... Myself: I've never let local standards stand in the way of my using international ones. - -- grok. - -- *** FULL-SPECTRUM DOMINANCE! *** * BOYCOTT BOURGEOIS MASS-MEDIA* RSS/XML newsfeeds from around * * Use these links in RSS readers * the planet: Who needs CNN/Fox? * Critical endorsement only Most sites need donations * http://rss.newstandardnews.net/iraq_1.xml Iraq in Crisis Newsfeed* * http://www.plenglish.com/rss/topstories/ PrensaTop News * * http://www.plenglish.com/rss/features/LatinaFeatures * * http://www.plenglish.com/rss/cuba/(Cuba)Cuba * * http://www.plenglish.com/rss/central/Central America * * http://www.plenglish.com/rss/caribbean/Caribbean * * http://www.plenglish.com/rss/south/South America * ** POSTMODERNISTS DECONSTRUCT. MARXISTS BUILD. ** GPG fingerprint = 2E7F 2D69 4B0B C8D5 07E3 09C3 5E8D C4B4 461B B771 -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (GNU/Linux) iD8DBQFExBTWXo3EtEYbt3ERAsBBAJ9vA4+2taneQwj2yJxmwnnHvEki/gCgiuGi IRm2oN7C4GOMCk13kAOZhYM= =Y+S4 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
