Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Query Select time within last hour.

Posted on 2013-11-06
11
Medium Priority
?
421 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
  • +1
11 Comments
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 39628640
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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 100 total points
ID: 39628725
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
ID: 39628754
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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 39628806
does GPSTime work or is that another int?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39628846
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39628939
You'd have to give some examples of int datetimes and the corresponding calendar datetime.
0
 

Author Comment

by:PeelSeel2
ID: 39630142
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 41

Expert Comment

by:Kyle Abrahams
ID: 39631747
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 49

Accepted Solution

by:
PortletPaul earned 100 total points
ID: 39632220
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 49

Expert Comment

by:PortletPaul
ID: 39632285
0
 

Author Closing Comment

by:PeelSeel2
ID: 39640018
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

618 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