CODEWITHSUNDEEP
sqlsql-serversql-server-2008
bibinmatthew
bibinmatthew

Reputation: 177

Take previous hour data in SQL

Hai guys i want to take previous hour data in SQL.

I have found something like this

DATEPART(hh,GETDATE())-1 –>

But it has some issues, since my date format is 24hr. It will give -ve value (-1) when the time is between 00.00.00-00.59.59 . I should get 23 as value. Any suggestions? This is what i did

DECLARE @hourlyaggregatedvolume smallint
DECLARE @hourlyaggregatedspeed smallint
DECLARE @hourlyaggregatedoccupancy float
DECLARE @hourlyaggregatedheadway float 
DECLARE @trafficdatadate datetime   
DECLARE @closeststationname nvarchar(100) = 'E100 1001'
DECLARE @accidentdate nvarchar(10) = '2014-02-17'
DECLARE @accidenttime nvarchar(10) = '01:10:00'
  SELECT 
           @hourlyaggregatedvolume = aggregated_volume,
           @hourlyaggregatedspeed  =aggregated_speed,
           @hourlyaggregatedheadway = aggregated_headway,
           @hourlyaggregatedoccupancy = aggregated_occupancy,
           @trafficdatadate = [date] 
           FROM dbo.traffic_data_aggregated_hourly    
           WHERE station = @closeststationname  
       AND CONVERT(DATE,[date]) = @accidentdate
       AND period = CASE WHEN SUBSTRING (@accidenttime,1,2) - 1 = -1 THEN 23
                    ELSE SUBSTRING (@accidenttime,1,2) - 1
                    END
       SELECT  @hourlyaggregatedvolume,@hourlyaggregatedspeed,@hourlyaggregatedheadway
       ,@hourlyaggregatedoccupancy,@trafficdatadate

Upvotes: 0

Views: 4971

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

Do date maths first, then extract the hour:

DATEPART(hour,DATEADD(hour,-1,GETDATE()))

(Also, I hate the "short" forms of the date components. I'd always rather spell it out in full e.g. hour versus hh because I find it more readable, it's usually not much more than a few characters more, and I can never remember which ones are for months vs minutes)

References: DATEADD, DATEPART, GETDATE

Upvotes: 6

marc_s
marc_s

Reputation: 754348

You probably want something like this:

DECLARE @Now DATETIME = CURRENT_TIMESTAMP
DECLARE @FromTimeStamp DATETIME = DATEADD(HOUR, -1, @Now)

SELECT 
    (list of columns)
FROM
    dbo.YourTableNameHere
WHERE
    SomeDateColumn >= @FromTimeStamp

or something similar - this selects whatever data columns you want from whatever table you have for the past hour (now minus 1 hour all the way through now).

The DATEADD function will handle all the pesky details of date/time math - if you run this 30 minutes after midnight, it will handle going back to 11:30pm yesterday just fine.

Upvotes: 1

Related Questions