Solved

How to Group by an HOUR - SQL query help needed

Posted on 2004-10-07
12
5,159 Views
Last Modified: 2008-02-01
Hi,
the question is probably not as easy as the subject looks like.
In MS Access I created a view, which I need to upsize to MS SQL (MSDE) now. But I used a way which is not supported in MSSQL.
Here it comes:

I have a table "logs" with fields - ID, log_date, issue
1     23.09.2004 11:50:52     aaa
2     23.09.2004 11:50:53     bbb
3     23.09.2004 12:10:25     ccc

now I have a view querry:

SELECT ID, Year([log_date]) & "/" & Month([log_date]) & "/" & Day([log_date]) & " " & HOUR([log_date]) & ":00:00" AS New_Date, issue FROM logs;

which returns
1     23.09.2004 11:00:00     aaa
2     23.09.2004 11:00:00     bbb
3     23.09.2004 12:00:00     ccc


NOW, I need to do this in MSSQL, but there are two issues:
- first I do not have a HOUR function, which will return the hour from a date/time stamp
- second, I do not want to use this amateur construction, as I believe, there must be a btter way how to convert a date/time stamp to a new one, which gets rid of the minutes and seconds part of the original date/time stamp (or later also get rid of the hour as well and leaves only the date)

The reason, I need to do queries then, which are GROUPED BY the whole HOUR, i.e. I will then receive from above example such grouped recordset

IDcount      Groupeddatebyhour
2               23.09.2004 11:00:00
1               23.09.2004 12:00:00

Hope, I described it clearly.

Thanks for any help.

0
Comment
Question by:keson
  • 4
  • 3
  • 3
  • +1
12 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 12247094
This should work, you where in fact very close:

SELECT count(*), Year([log_date]) & "/" & Month([log_date]) & "/" & Day([log_date]) & " " & HOUR([log_date]) & ":00:00" AS New_Date, issue FROM logs
group by Year([log_date]) & "/" & Month([log_date]) & "/" & Day([log_date]) & " " & HOUR([log_date]) & ":00:00"

CHeers
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 12247114
hour functino is exists in sql....  but datepart is exists....

SELECT count(*), datepart(year, [log_date]) & "/" & datepart(month, ([log_date]) & "/" & datepart(day, [log_date]) & " " & datepart(hour, [log_date]) & ":00:00" AS New_Date, issue FROM logs
group by datepart(year, [log_date]) & "/" & datepart(month, ([log_date]) & "/" & datepart(day, [log_date]) & " " & datepart(hour, [log_date]) & ":00:00"

itsvtk
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 12247123
From sql server books online.....


DATEPART
Returns an integer representing the specified datepart of the specified date.

Syntax
DATEPART ( datepart , date )

Arguments
datepart

Is the parameter that specifies the part of the date to return. The table lists dateparts and abbreviations recognized by Microsoft® SQL Server™.

Datepart                                   Abbreviations
year                                         yy, yyyy
quarter                                     qq, q
month                                       mm, m
dayofyear                                  dy, y
day                                           dd, d
week                                        wk, ww
weekday                                   dw
hour                                         hh
minute                                      mi, n
second                                     ss, s
millisecond                               ms


The week (wk, ww) datepart reflects changes made to SET DATEFIRST. January 1 of any year defines the starting number for the week datepart, for example: DATEPART(wk, 'Jan 1, xxxx') = 1, where xxxx is any year.

The weekday (dw) datepart returns a number that corresponds to the day of the week, for example: Sunday = 1, Saturday = 7. The number produced by the weekday datepart depends on the value set by SET DATEFIRST, which sets the first day of the week.

date

Is an expression that returns a datetime or smalldatetime value, or a character string in a date format. Use the datetime data type only for dates after January 1, 1753. Store dates as character data for earlier dates. When entering datetime values, always enclose them in quotation marks. Because smalldatetime is accurate only to the minute, when a smalldatetime value is used, seconds and milliseconds are always 0.

If you specify only the last two digits of the year, values less than or equal to the last two digits of the value of the two digit year cutoff configuration option are in the same century as the cutoff year. Values greater than the last two digits of the value of this option are in the century that precedes the cutoff year. For example, if two digit year cutoff is 2049 (default), 49 is interpreted as 2049 and 2050 is interpreted as 1950. To avoid ambiguity, use four-digit years.

For more information about specifying time values, see Time Formats. For more information about specifying dates, see datetime and smalldatetime.

Return Types
int

Remarks
The DAY, MONTH, and YEAR functions are synonyms for DATEPART(dd, date), DATEPART(mm, date), and DATEPART(yy, date), respectively.

Examples
The GETDATE function returns the current date; however, the complete date is not always the information needed for comparison (often only a portion of the date is compared). This example shows the output of GETDATE as well as DATEPART.

SELECT GETDATE() AS 'Current Date'
GO

Here is the result set:

Current Date                
---------------------------
Feb 18 1998 11:46PM        

SELECT DATEPART(month, GETDATE()) AS 'Month Number'
GO

Here is the result set:

Month Number
------------
2            

This example assumes the date May 29.

SELECT DATEPART(month, GETDATE())
GO

Here is the result set:

-----------
5          

(1 row(s) affected)

In this example, the date is specified as a number. Notice that SQL Server interprets 0 as January 1, 1900.

SELECT DATEPART(m, 0), DATEPART(d, 0), DATEPART(yy, 0)

Here is the result set:

----- ------ ------
1     1      1900


itsvtk
0
 
LVL 14

Accepted Solution

by:
Thandava Vallepalli earned 40 total points
ID: 12247143

SELECT count(*), datepart(year, [log_date]) + "/" + datepart(month, ([log_date]) + "/" + datepart(day, [log_date]) + " " + datepart(hour, [log_date]) + ":00:00" AS New_Date, issue FROM logs
group by datepart(year, [log_date]) + "/" + datepart(month, ([log_date]) + "/" + datepart(day, [log_date]) + " " + datepart(hour, [log_date]) + ":00:00"

itsvtk
0
 
LVL 17

Assisted Solution

by:BillAn1
BillAn1 earned 35 total points
ID: 12247302
the most 'efficient' method is to use CONVERT function to do the formatting for you.
Since your example uses a non-standard format of  "yy/mm/dd HH:00:00" you will need to use 2 formats (11 - yy/mm/dd and 108 - HH:mm:ss) and concatenat them together, and truncate the 108 format to only 3 characters :
If you use the DATEPART functions etc, you still ahve to explicitly cast all the numbers to strings, and worry about inserting leading 0's etc

select count(*),
convert(char(8), log_date, 11) + ' ' + convert(char(3),log_date, 108) + '00:00'
from logs
group by convert(char(8), log_date, 11)+ ' ' + convert(char(3),log_date, 108) + '00:00'

however, if you just want the output in a standard datetime format, you can do it with one conversion :

select count(*),
convert(char(13),log_date, 20) + ':00:00'
from logs
group by convert(char(13),log_date, 20) + ':00:00'


0
 

Author Comment

by:keson
ID: 12247597
Guys,

thanks a lot. I knew it must be possible. I will have to study those options for convert as I see one can do much more with those.

If I can trouble you again, please, have a look at my next post, which is related to this issue and I want give away another 100 points for a sollution.
It is about SQL querry which will probably save me hours of try/error. Just wait till I sum it in a question.

Thanks again for your help.

cheers
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 12247663
seems this time i was too fast to get noticed / points :-)

PS: don't worry about points!!!
0
 

Author Comment

by:keson
ID: 12247965
to angelIII,
sorry mate. You were fast enough and your answer was correct. Only I needed sollution for MS SQL server. The one I had now looks exactly the way you did it. But it does not work under the blooddy MS stuff. So I gave points to those who brought me a new stuff this time. Well, you will have a chance with the next question, I am still unable to form the way you will be able to understand it.

Cheers!

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 12248082
Indeed, sorry for that.
Ok, let me suggest this solution for literally removing minutes and seconds from your date/time

This would be the view query:
SELECT ID, dateadd ( minute, - datepart( minute, log_date) ,dateadd(second, -datepart(second,log_date), log_date)) AS New_Date, issue FROM logs;

similary, the GROUP BY:
SELECT count(*), dateadd ( minute, - datepart( minute, log_date) ,dateadd(second, -datepart(second,log_date), log_date)) AS New_Date, issue FROM logs
GROUP BY dateadd ( minute, - datepart( minute, log_date) ,dateadd(second, -datepart(second,log_date), log_date)) ;

This will remove the necessity to do several conversions which for sure are less efficient than this one.

Anyhow, glad we could help
0
 

Author Comment

by:keson
ID: 12248996
to: angelIII,

Great! I didn't even expect that such thing is possible. I can then indeed get rid of virtually any part of the date string for a specific "group by" needs! Right?

I have to say honestly, at the moment I do not understand what does the query you wrote above do, but I will try to understand it part by part.

Anyway.

My initial intention was to design an ASP code, which can run on both MS Access (in test env.) as well as on MS SQL database in live env. However, I am experiencing, that there are functions in both databases which are noth supported in the other one. Like the dateadd, I guess is not supported in MS Access.

I will have to probably do a global variable which will decide whether the database is MS Access or MS SQL and do two different queries for each one.

Or any other idea for Multidatabase code?

P.S. Can I give you just so few points?

Keson
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 12249114
>>I can then indeed get rid of virtually any part of the date string for a specific "group by" needs! Right?
Yes

>>Or any other idea for Multidatabase code?
not really. Have tried doing this for oracle/sql server, I have ended up doing 2 implementations of a "STORAGE" class, each computes the SQL necessary as requested... Trying to stay compatible is senseless, as you defeat the optimization possiblities of the different products...

>>I have to say honestly, at the moment I do not understand what does the query you wrote above do, but I will try to understand it part by part.
No problem, just start off this query:
select getdate(), datepart(minute, getdate()), dateadd(minute, -datepart(minute, getdate()), getdate())
The first column displays the current date/time, the second the actual minute value. The last just sets the minute value to 0, similar to what I suggested in the query for you.

>>P.S. Can I give you just so few points?
As said, don't matter the points. Getting ~20-50K points per month, so I don't mind them anyhow.

CHeers

0
 
LVL 17

Expert Comment

by:BillAn1
ID: 12250302
be careful using this structure.
Subtracting the minutes / secods will NOT give you an exact hour, as there are still miliseconds stored in the datetime. When you display it,  e.g. select getdate(), datepart(minute, getdate()),
varchar(100),dateadd(ss, - datepart(ss, getdate()), dateadd(minute, -datepart(minute, getdate()), getdate()))
it may look like it has been rounded correctly, but this is only the display not showing you the full milisecs correctly. if you were to use this in a group by you would get multiple records, not one.
to hilight this, use format 121 to display the full value including ms -
select getdate(), datepart(minute, getdate()),
convert(varchar(100),dateadd(ss, - datepart(ss, getdate()), dateadd(minute, -datepart(minute, getdate()), getdate())),121)

again, the cleanest way to truncate to hour is to use the convert function : select convert(char(13),getdate(), 20) + ':00:00'
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now