SQL Getting data by the hour

Question

Hi I have a weather database in SQL Server 2008 that is filled with weather observations that are taken every 20 minutes. I want to get the weather records for each hour not every 20 minutes how can I filter out some the results so only the first observation for each hour is in the results.

Example:

7:00:00
7:20:00
7:40:00
8:00:00

Desired Output

7:00:00 
8:00:00

Does every hour have rows for each 20min increment, or are there any gaps? — Michael Berkowski, Dec 28 '15 at 23:32
Apply a WHERE condition like that: `WHERE datepart(minute, hour_column) = `. In your case that would be 00 for each hour. — Kamil Gosciminski, Dec 28 '15 at 23:39

Matthew Haugen · Accepted Answer · 2015-12-28T23:48:01.060

To get exactly (less the fact that it's an INT instead of a TIME; nothing hard to fix) what you listed as your desired result,

SELECT DISTINCT DATEPART(HOUR, TimeStamp)
FROM Observations

You could also add in CAST(TimeStamp AS DATE) if you wanted that as well.

Assuming you want the data as well, however, it depends a little, but from exactly what you've described, the simple solution is just to say:

SELECT *
FROM Observations
WHERE DATEPART(MINUTE, TimeStamp) = 0

That fails if you have missing data, though, which is pretty common.

If you do have some hours where you want data but don't have a row at :00, you could do something like this:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY CAST(TimeStamp AS DATE), DATEPART(HOUR, TimeStamp) ORDER BY TimeStamp)
    FROM Observations
)

SELECT *
FROM cte
WHERE n = 1

That'll take the first one for any date/hour combination.

Of course, you're still leaving out anything where you had no data for an entire hour. That would require a numbers table, if you even want to return those instances.

score 0 · Answer 2 · answered Dec 28 '15 at 23:38

You can use a formula like the following one to get the nearest hour of a time point (in this case it's GETUTCDATE()).

SELECT  DATEADD(MINUTE, DATEDIFF(MINUTE, 0, GETUTCDATE()) / 60 * 60, 0)

Then you can use this formula in the WHERE clause of your SQL query to get the data you want.

score 0 · Answer 3 · answered Dec 28 '15 at 23:41

0

What you need is to GROUP BY your desired time frame, like the date and the hours. Then, you get the MIN value of the timeframe. Since you didn't specify which columns you are using, this is the most generic thing i can give.

answered Dec 28 '15 at 23:41

stb

3,405
2
17
24

score 0 · Answer 4 · answered Dec 28 '15 at 23:44

0

Use as filter :

... where DATEPART(MINUTE, DateColumn) = 0

answered Dec 28 '15 at 23:44

Didier68

1,027
12
26

score 0 · Answer 5 · answered Dec 28 '15 at 23:51

To filter the result for every whole hour, you can set your where clause to check for 00 minute since every whole hour is HH:00:00.

To get the minute part from a time-stamp, you can use DATEPART function.

SELECT * 
FROM YOURTABLENAME
WHERE DATEPART(MINUTE, YOURDATEFIELDNAME) = 0

More information on datepart function can be found here: http://www.w3schools.com/sql/func_datepart.asp

SQL Getting data by the hour

5 Answers5