[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Query Select time within last hour.

Posted on 2013-11-06
11
Medium Priority
?
425 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 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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

830 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