Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 429
  • Last Modified:

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.
0
PeelSeel2
Asked:
PeelSeel2
  • 3
  • 3
  • 3
  • +1
2 Solutions
 
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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
 
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.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now