how do you group by days or weeks?

sfullman

how in mySQL would you group by days or weeks (or months etc) when the field I want to group on is a timestamp, not a simple date.

this probably is a simple mysql function.. thanks

bradgrafelman

sfullman;10960013 wrote:
this probably is a simple mysql function..

Indeed; as such, I'd probably start with the MySQL manual page entitled Date and Time Functions

Krik

TIMESTAMP is a unix time stamp of the number of second from January 1 1970 00:00:00 GMT.

So if you use ORDER BY, in your query, on that column the results will be sorted by seconds. And all the second from last week will be grouped together. And all the seconds from 3 months ago will be grouped together. And all the seconds from today will be grouped together.

Every entry will be sorted from the oldest to the newest based on the seconds that are in the TIMESTAMP'ed column.

EDIT: beat me to it 😃

bradgrafelman

TIMESTAMP in the context of MySQL is a column storing a date and time in YYYY-MM-DD HH:MM:SS format.

EDIT: That is, of course, assuming that the datetime was stored properly and not as an integer. Otherwise, you'd have to combine another MySQL date and time function to convert the integer into something that's actually usable (which should be a big hint as to why you shouldn't be storing the integer in the first place :p).

Krik

Your right about TIMESTAMP when he used "timestamp" in his question that got me thinking that way.

As well his comment of "not a simple date" made me think it has to be a unix timestamp.

Guess it goes to show how 2 people can read the same thing and come to conclude 2 different things.

EDIT: I hope the date isn't stored as an integer that would really make thing more complicated than necessary.

sfullman

the timestamp field is necessary because you need to know the time the record was inserted.

But, reports needed come by the day.

so yeah, I could do:

"SELECT whatever FROM table GROUP BY DATE_FORMAT('%Y%m%d', EntryTime)

or some approximation of that, BUT we're talking millions of records. And that seems to be very clumsy as it's doing probably a string conversion.

I tried GROUP BY CURDATE(EntryTime) but curdate() doesn't take a parameter it appears.

We should all be allowed 5 coupons on this forum for "free advice without having to do research just 'cause the guy helping you is smart" 🙂 If so, I'd like to present my coupon now. Think I'll go honor some coupons like this on the coding forum..