SQL Query Select time within last hour.

I have the following query:

SELECT Vehicle_ID
     , Alias
       , Day
       , Month
       , Year
       , GPSTime
       , Date_Time
       , PC_Date
       , PC_Time
       , Street
       , City
       , State
       , Zip_Code
       , Latitude
       , Longitude
       , Speed
       , Course
       , Altitude
       , Event
       , Advisory_Event
      
FROM            dbo.VehicleState
WHERE        (Latitude IS NOT NULL)

The Alias is our trucks, of which we have about 25 of them.  They report in a postion, date and time.  I want to take and select the record that reflects the last time within the last hour that each truck has reported in.  I had this working through Access, but my SQL-Fu is not enough to do it in SQL 2012.  Any help is appreciated.
PeelSeel2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Kyle AbrahamsSenior .Net DeveloperCommented:
SELECT Vehicle_ID
     , Alias
       , Day
       , Month
       , Year
       , GPSTime
       , Date_Time
       , PC_Date
       , PC_Time
       , Street
       , City
       , State
       , Zip_Code
       , Latitude
       , Longitude
       , Speed
       , Course
       , Altitude
       , Event
       , Advisory_Event
       
FROM            dbo.VehicleState
WHERE        (Latitude IS NOT NULL) and Date_Time >= dateadd(hh, -1, getdate())
/* you said you want the latest . . . if there is more than one in the last our this needs to be included, otherwise you can remove below this and just keep the addition in the where condition */
group by     Vehicle_ID
        , Alias
       , Day
       , Month
       , Year
       , GPSTime
       , Date_Time
       , PC_Date
       , PC_Time
       , Street
       , City
       , State
       , Zip_Code
       , Latitude
       , Longitude
       , Speed
       , Course
       , Altitude
       , Event
       , Advisory_Event
having Date_Time  = max(date_time)
0
 
Scott PletcherSenior DBACommented:
SELECT Vehicle_ID
     , Alias
       , Day
       , Month
       , Year
       , GPSTime
       , Date_Time
       , PC_Date
       , PC_Time
       , Street
       , City
       , State
       , Zip_Code
       , Latitude
       , Longitude
       , Speed
       , Course
       , Altitude
       , Event
       , Advisory_Event
FROM (
SELECT Vehicle_ID
     , Alias
       , Day
       , Month
       , Year
       , GPSTime
       , Date_Time
       , PC_Date
       , PC_Time
       , Street
       , City
       , State
       , Zip_Code
       , Latitude
       , Longitude
       , Speed
       , Course
       , Altitude
       , Event
       , Advisory_Event
       , ROW_NUMBER() OVER(PARTITION BY Alias ORDER BY GPS_Time DESC) AS row_num      
FROM            dbo.VehicleState
WHERE        (Latitude IS NOT NULL) AND
                    GPS_Time >= DATEADD(HOUR, -1, GETDATE())
) AS derived
WHERE
    row_num = 1
0
 
PeelSeel2Author Commented:
I got the following error:

Arithmetic overflow error converting expression to data type datetime.

I looked at our vendors table and they have date_time as an INT, and not DATETIME.  So I tried cast ( date_time as datetime) but still same error.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Kyle AbrahamsSenior .Net DeveloperCommented:
does GPSTime work or is that another int?
0
 
PortletPaulfreelancerCommented:
If [date_time] and [GPS_time] are integre then one converts getdate() to int for the comparisons to work.

You mention SQL 2012, so you can use format()

e.g.
       FORMAT(GETDATE(), 'yyyyMMddHHmmss')

and:

cast(FORMAT(GETDATE(), 'yyyyMMddHHmmss') as bigint)
cast(FORMAT(GETDATE(), 'yyyyMMddHHmm') as bigint)

You don't indicate how large the [date_time] or [GPS_time] values are. I have gone down to seconds in the example above (14 digits) and this requires bigint. Removing 'ss' would result in minute precision and 12 digits still needing bigint.
If [date_time] or [GPS_time] values are only 8 digits then you cannot determine "in the last hour" from that/those field/s.
0
 
Scott PletcherSenior DBACommented:
You'd have to give some examples of int datetimes and the corresponding calendar datetime.
0
 
PeelSeel2Author Commented:
Example of all date related info from DB for one row.

Date_Time:
1383595291 int

PC_date:
2013-11-04 varchar(12)

PC_Time:
14:01:33 char(8)

Day:
4 smallint

Month:
11 smallint

Year:
2013 smallint

GPSTime:
50491 float
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
How do you know that 1383595291 = 11/04/2013?

I thought it was a Julian date but it's not working.  Have you worked with that datetime column in the past?
0
 
PortletPaulfreelancerCommented:
SELECT
        *

FROM   (
         SELECT
               *
               , row_number() over (partition BY Vehicle_ID ORDER BY [Date_Time] DESC) AS rn
         FROM dbo.VehicleState
         
         WHERE  Latitude IS NOT NULL
         
         /* and within the last hour is provided by the following */
         AND [Date_Time] > datediff(SECOND,'1970-01-01',dateadd(hour,-1,getdate()))
         
       ) AS VS
/* "the record that reflects the last time" is achieved by using row_number()
, partitioned by vehicle_id and sorted DESCending */
WHERE rn = 1

Open in new window

Your [Date_Time] field is a "Unix timestamp" I believe which is the number of seconds since 1970-01-01
e.g.
select dateadd(second,1383595291,'1970-01-01')

returns "November, 04 2013 20:01:31"

So the suggested method for getting data "within the last hour" deducts 1 hour from getdate(), then calculates the number of seconds from 1970-01-01 to that.
Then this integer result can be directly compared to the [Date_Time] field.

Now it gets a bit more interesting:
That unix timestamp 1383595291 is not  '2013-11-04 14:01:33'
There is a 6 hour 2 second difference

Could there be any reason for this difference?

You might want to run the following, it will calculate the seconds between the [Date_Time] and the [PC_date] + [PC_Time]
(both get converted to datetime)

SELECT
      pc_datetime
    , unix_datetime
    , datediff(SECOND,pc_datetime,unix_datetime) seconds_diff
FROM VehicleState
CROSS apply (SELECT
                     /* convert 2 x varchars to datetime */
                cast(pc_date AS datetime) + cast(pc_time AS datetime)

                    /* convert the unix timestamp to datetime */
              , dateadd(SECOND,[Date_Time],'1970-01-01')

             ) AS ca1 (pc_datetime, unix_datetime)

/* adjust where conditions to suit */
WHERE [Date_Time] > datediff(SECOND,'1970-01-01',dateadd(DAY,-1,getdate()))

Open in new window

Finally I'd have to say it's a pity you have so many date and time related fields but none of them are in a data type the sql server recognizes as date or time.

I would suggest you consider adding a computed column(s) that will provide you with datetime values in your table. Then you won't have to perform a bagful of tricks in every query.
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
PortletPaulfreelancerCommented:
0
 
PeelSeel2Author Commented:
Thanks!!  Based off two posts here is the solution I came up with:

SELECT [Vehicle_ID]
      ,[Day]
      ,[Month]
      ,[Year]
      ,[GPSTime]
      ,[Date_Time]
      ,[PC_Date]
      ,[PC_Time]
      ,[Latitude]
      ,[Longitude]
      ,[Speed]
      ,[Course]
      ,dateadd(second,GPSTime,PC_date) as GPS_Time
 
  FROM [Integrator].[dbo].[VehicleState]

  WHERE LATITUDE > 0 AND dateadd(second,GPSTime,PC_date) >= dateadd(hh, -1, getdate())

  GROUP BY [Vehicle_ID]
      ,[Day]
      ,[Month]
      ,[Year]
      ,[GPSTime]
      ,[Date_Time]
      ,[PC_Date]
      ,[PC_Time]
      ,[Latitude]
      ,[Longitude]
      ,[Speed]
      ,[Course]
      ,dateadd(second,GPSTime,PC_date)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.