SQL to group data by Day

[SDB]

Hi

I'm new to unix time format, could someone point me in the right direction so I can produce a query which groups data based on a timestamp field by calendar day?

Thanks in advance.

Simon

[Eikinskjaldi]

Hi

I'm new to unix time format, could someone point me in the right direction so I can produce a query which groups data based on a timestamp field by calendar day?

Thanks in advance.

Simon

select blah, day(from_unixtime(timestamp_field)) as 'days' from sometable group by days

[SDB]

Perfect!

Thank you very much indeed, that's got me started.

Simon

[vertigo jones]

Won't that group Jan 1 with March 1 and May 1 etc...?

[Eikinskjaldi]

Won't that group Jan 1 with March 1 and May 1 etc...?

He said he wanted it grouped by calendar day. At any rate, I daresay that now he knows about from_unixtime, he will be able to sort out which date function he needs.

[vertigo jones]

He said he wanted it grouped by calendar day. At any rate, I daresay that now he knows about from_unixtime, he will be able to sort out which date function he needs.

I wasn't trying to give you crap about it, I wasn't exactly sure if that's what he was asking for either.

[SDB]

sorry guys, I missed this..

Yeah, I had a quick play, realised that, as you say it grouped by day of the month, so just added year and month fields, and sorted by them as well.

Thank to both :)

-

For anyone reading this and still struggling, use :

year(from_unixtime(timestamp_field)) as year, month(from_unixtime(timestamp_field)) as month, day(from_unixtime(timestamp_field)) as day...

then

group by year, month, day
order by year, month, day