[thelist] Incorrect SQL?
rudy
r937 at interlog.com
Sat Apr 6 10:35:01 CST 2002
> Can anyone see what's wrong with it?
yes, the GROUP BY is incorrect --
SELECT entryTitle
, DATE_FORMAT(entryDate,'%d-%m-$Y') as entryDate
, DATE_FORMAT(entryDate,'%W, %M %D') as entryLongDate
, DATE_FORMAT(entryDate,'%T') as entryTime
, entryEntry
, count(*) as entryComments
FROM blogEntries, blogComments
WHERE blogEntries.entryId = blogComments.entryId
GROUP BY entryTitle, entryDate, entryEntry
ORDER BY entryDate DESC LIMIT 7
the GROUP BY must list all the non-aggregate fields in the SELECT
not sure if mysql will allow it, but based on the above query, it should be
GROUP BY entryTitle
, DATE_FORMAT(entryDate,'%d-%m-$Y')
, DATE_FORMAT(entryDate,'%W, %M %D')
, DATE_FORMAT(entryDate,'%T')
, entryEntry
or alternatively
GROUP BY entryTitle
, entryDate
, entryLongDate
, entryTime
, entryEntry
count(*), joshua, counts rows in each group
> a) If I'm putting in now() for the value of datetime,
> how do I tell it to add n hours to the time?
use the mysql DATE_ADD function
> b) how many hours are evolt's servers relative to gmt,
> ie how much will I need to adjust the hours for australian time?
good question
evolt's servers are gmt-6, i believe, but i'm not sure how you should
calculate dates and times over the day boundary
and don't forget there's daylight saving time to contend with, north
america springs forward tonight
rudy
More information about the thelist
mailing list