Jump to content


Photo

Tricky 'GROUP BY' MySQL question


  • Please log in to reply
3 replies to this topic

#1 webbgroup

webbgroup
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 16 January 2003 - 02:43 AM

How would you group by dates in a table where the date shows up like this: \"2003-01-15 13:05\"

I already tried something like this without much success.

select time as TIME, count(*) as posts from table group by time REGEXP \"2003-01-%\";

Any suggestions??

#2 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 16 January 2003 - 10:35 AM




mysql> SELECT DATE_FORMAT(\'1997-10-04 22:23:00\', \'%Y-%m-%d\');

+------------------------------------------------+

| DATE_FORMAT(\'1997-10-04 22:23:00\', \'%Y-%m-%d\') |

+------------------------------------------------+

| 1997-10-04                                     |

+------------------------------------------------+

1 row in set (0.00 sec)



mysql> 




Use the date_format consistently throughout the query.

I think you should read the mysql manual about date functions:

http://www.mysql.com..._functions.html

P., denmark
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center

#3 webbgroup

webbgroup
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 16 January 2003 - 04:18 PM

YOur the man! Thanks for your help

#4 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 16 January 2003 - 07:06 PM

bio keeps the sql kicking :D
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users