Grouping by hour 1

[lhg1]

Hi

I'm trying to make a grouping by hour.

The date is set as an integer, but that conversion is fine.

I would like output like this
Day Amount
01-01-2010 10
02-01-2010 12
03-01-2010 9

In ORACLE something like this

SELECT to_char(date, 'yyyy-mm-dd hh24') "Dato",
       COUNT(*)
FROM table
GROUP BY to_char(date, 'yyyy-mm-dd hh24')

I've managede to do it in months

SELECT
MONTH(CONVERT(VARCHAR, DATEADD(ss, create_date - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120)) AS MONTH, 
count(*) AS Antal
from table
where CONVERT(VARCHAR, DATEADD(ss, create_date - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120)> dateadd(hh,-168,getdate())
GROUP BY MONTH(CONVERT(VARCHAR, DATEADD(ss, create_date - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120))

But apparently there is no HOUR keyword.

Hope someone can help.

/Lhg

 

[gmmastros]

But apparently there is no HOUR keyword.

No. But there is a DatePart function that can return this data.

Select DatePart(hour,GetDate())

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[r937]

the HOUR function in SQL Server is implemented as one of the options in the DATEPART function



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[r937]

aaargh, sniped by half a second !!!

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[gmmastros]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

What about something like:

select CONVERT(VARCHAR, mydate, 101) + ' ' + left(convert(varchar, myDATE, 114), 2) AS Dato, count(*) as antal
from table
where ...
GROUP BY select CONVERT(VARCHAR, mydate, 101) + ' ' + left(convert(varchar, myDATE, 114), 2)

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[djj55]

Datepart would be better. I am too slow at my answers.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[gmmastros]

The date is set as an integer, but that conversion is fine.

From your code, it appears as though you are using a unix date time which is stored as the number of seconds since Jan 1, 1970. If this is true, you can get the HOUR by performing some very simple calculations. This will be many times faster that converting to date and using a DatePart function.

To calculate the hour of a unix date time integer...

[tt][blue]
Select (UnixDateTime % 86400) / 3600 As Hour
[/blue][/tt]



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[lhg1]

Hi

Thanks
I ended up with this SQL (it works, eventhou is long)

SELECT
YEAR(CONVERT(VARCHAR, DATEADD(ss, create_date - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120)) As year,
MONTH(CONVERT(VARCHAR, DATEADD(ss, create_date - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120)) as Month,
DatePart(day,CONVERT(VARCHAR, DATEADD(ss, create_date - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120)) as day,
DatePart(hour,CONVERT(VARCHAR, DATEADD(ss, create_date - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120)) as Hour,
count(*) as Amount
from table
where CONVERT(VARCHAR, DATEADD(ss, create_date - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120)> dateadd(hh,-24,getdate())
GROUP BY 
YEAR(CONVERT(VARCHAR, DATEADD(ss, create_date - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120)),
MONTH(CONVERT(VARCHAR, DATEADD(ss, create_date - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120)),
DatePart(day,CONVERT(VARCHAR, DATEADD(ss, create_date - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120)),
DatePart(hour,CONVERT(VARCHAR, DATEADD(ss, create_date - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120))
order by 
YEAR(CONVERT(VARCHAR, DATEADD(ss, create_date - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120)),
MONTH(CONVERT(VARCHAR, DATEADD(ss, create_date - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120)),
DatePart(day,CONVERT(VARCHAR, DATEADD(ss, create_date - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120)),
DatePart(hour,CONVERT(VARCHAR, DATEADD(ss, create_date - DATEDIFF(ss, GETDATE(), GETUTCDATE()), '1970-01-01 00:00'), 120))

Only problem.

When ist hour 00 is does not displat anything...

Output.
year Month day Hour Antal
2010 3 18 16 1
2010 3 18 17 1
2010 3 18 18 1
2010 3 18 23 1
2010 3 19 2
2010 3 19 8 1

Thanks
LHG

 

[r937]

first of all, you can greatly simplify your query by using a subquery ---

SELECT theyear
     , themonth    
     , theday
     , thehour
     , COUNT(*) AS amount
  FROM ( SELECT YEAR(thedate) AS theyear
              , MONTH(thedate) AS themonth
              , DATEPART(DAY,thedate) AS theday
              , DATEPART(HOUR,thedate) AS thehour
           FROM ( SELECT DATEADD(SS,create_date 
                                   - DATEDIFF(SS,GETDATE()
                                                ,GETUTCDATE())
                             , '1970-01-01 00:00') AS thedate
                ) AS dt1
          WHERE thedate > DATEADD(HH,-24,getdate())
       ) AS dt2
GROUP 
    BY theyear
     , themonth    
     , theday
     , thehour
ORDER
    BY theyear
     , themonth    
     , theday
     , thehour

as for why there are no transactions for hour=00, maybe your sample size isn't big enough

rather than just one day's worth of data, try running the query for a couple of weeks of data

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[PDreyer]

Don't use functions on create_date in your where clause else it won't be able to use the index
I suggest

select dateadd(hh ,datediff(hh ,'' ,dateadd(ss,create_date-DATEDIFF(ss, GETDATE(), GETUTCDATE()),'19700101') ),'') as 'Dato'
,count(*) as 'amount'
from daTable
where create_date>datediff(ss,'19700101',dateadd(hh,-24+DATEDIFF(hh, GETDATE(), GETUTCDATE()),getdate()) )
group by dateadd(hh ,datediff(hh ,'' ,dateadd(ss,create_date-DATEDIFF(ss, GETDATE(), GETUTCDATE()),'19700101') ),'')

 

[lhg1]

Hi

I'm running this throu a PHP adapter, and I think thats the reason for the blank result.

I've solvede this in PHP.

Thanks.

LHG

 

[NastiaMurzin]

use tableschema;
go
create procedure Createtable3
as
create table Hour2
(Day date null,
Amount date as primary key not null);
go
insert into Hour2 values (cast('01-01-2009' as datetime as 'Day'), cast('12:00:00' as datetime as 'Amount'));
go
insert into Hour2 values ('01-01-2009', '10:00:00');
go
insert into Hour2 values('02-01-2009', '12:00:00')'
go
insert into Hour2 values('03-01-2009', '9:00:00')'
go
select * from Hour2;
go


You can use the date function to find the current date, or u can put the values in yourself for the table.