[Resolved] mysql day() not existing?

[leatherback]

Hi,

I have a timastamp field (YYYYmmddHHiiss).
I now would like to query it like:

select count(*) from table group by day(time)

But somehow I cannot figure out why this doesn't work, unless the day function really doesn't exist (Couldn't find it in mysql.com).

Anybody any other ideas? (besides the option of extraction a unix timestamp, and looping thourgh it to calculate days)

Thanks!

J

[Norman_Graham]

Hi Leatherback

The day() function doesn't exist. You just need to format the timestamp:

SELECT FORMAT(mytimestamp, %W) as day FROM mytable GROUP BY day;

The %W extracts the day of the week from the timestamp. There are myriad other things you can do with dates and times in MySQL. Try this page of the manual:
http://www.mysql.com/doc/en/Date_and_time_functions.html

Best o luck

Norm

[leatherback]

Hi orma,

Thanks for your reply.

Yeah.. I know all about the date / time options. I just couldn't believe there is a year() month() hour() minute() second() and not a day()

I'll play with it.

THanks,

J.