# SQL Query Timestamp group by date

Discussion in 'Computer Science & Culture' started by one_raven, May 27, 2008.

Not open for further replies.
1. ### one_ravenGod is a Chinese WhisperValued Senior Member

Messages:
13,406
I have a query in which I want to sort the results by date.
I only have a timestamp field (named: opened), with which to use.

An entry of the timestamp field looks like this:
5/11/2008 12:00:26 AM

Here is my query:

SELECT t.opened, (select count(environment) from Current where opened=t.opened) AS TOTAL, (select count(environment) from Current where environment="P" and opened=t.opened) AS PROD, (select count(environment) from Current where environment="C" and opened=t.opened) AS COB, (select count(environment) from Current where environment="U" and opened=t.opened) AS UAT, (select count(environment) from Current where environment="D" and opened=t.opened) AS DEV
FROM [Current] AS t
GROUP BY t.opened;

Unfortunately, it groups by the whole timestamp field, not by the date.
I want it to group by the date.

3. ### mapsdnasggeyergfubarRegistered Senior Member

Messages:
62
What database product are you using?

5. ### one_ravenGod is a Chinese WhisperValued Senior Member

Messages:
13,406
Right now it is in MS Access, but I will likely convert it to SQL.

7. ### nietzschefanThread KillerValued Senior Member

Messages:
7,721

GROUP BY t.opened, t(?).Date(?)

Oh...you don't have date..

I guess you need another select(~parse the date alone in the field imtoolazytodoit)

8. ### mapsdnasggeyergfubarRegistered Senior Member

Messages:
62
I don't know much about Access, it does seem to have a function DateValue that extracts the Date portion from the timestamp. You would replace every reference to opened with DateValue(opened).

As a side note I would write the SQL you gave in the following way (but since I don't know Access I am not sure if it would be valid).

Code:
select DateValue(opened) as OPENED,
count(environment) as TOTAL,
sum(case environment when 'P' then 1 else 0 end) as PROD,
sum(case environment when 'C' then 1 else 0 end) as COB,
sum(case environment when 'U' then 1 else 0 end) as UAT,
sum(case environment when 'D' then 1 else 0 end) as DEV
from [Current]
group by DateValue(opened)


9. ### one_ravenGod is a Chinese WhisperValued Senior Member

Messages:
13,406
Thanks.

I'll take a stab at it when I get back to work.

Messages:
3,336

Messages:
13,406