Solved

SQL Query Select time within last hour.

Posted on 2013-11-06
11
410 Views
Last Modified: 2013-11-11
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.
0
Comment
Question by:PeelSeel2
  • 3
  • 3
  • 3
  • +1
11 Comments
 
LVL 39

Expert Comment

by:Kyle Abrahams
Comment Utility
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
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 25 total points
Comment Utility
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
 

Author Comment

by:PeelSeel2
Comment Utility
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
 
LVL 39

Expert Comment

by:Kyle Abrahams
Comment Utility
does GPSTime work or is that another int?
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
You'd have to give some examples of int datetimes and the corresponding calendar datetime.
0
 

Author Comment

by:PeelSeel2
Comment Utility
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
 
LVL 39

Expert Comment

by:Kyle Abrahams
Comment Utility
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
 
LVL 48

Accepted Solution

by:
PortletPaul earned 25 total points
Comment Utility
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
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
0
 

Author Closing Comment

by:PeelSeel2
Comment Utility
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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

763 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