Home > IT Answers > Development > SQL Query for time data only
Help

Question

  Asked: Apr 17 2008   7:29 PM GMT
  Asked by: C Pete

SQL Query for time data only


SQL Query, SQL

I need to query a large remote weather database for events reported within time frames without regard for the day. For instance, I need all data that was reported for the fifteen minute window closest to noon each day for the last twelve years. I don't care about specific dates, just the daily time windows. How do I write this query to download only one record for each of thousands of days?

Thanx,
Pete

Subscribe to Alerts! Get questions and answers delivered to your Inbox.


E-mail me updates on this question



   SUBSCRIBE

hidden modal window
Help

Answer Wiki (Improve, edit or add to this answer)

IMPROVE THIS ANSWER
View History
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
Last Answered: Apr 18 2008  7:00 PM GMT  by Mrdenny




Assuming that your column has a data type of datetime you'll need to use a CONVERT statement in your WHERE clause to find the data you are looking for.

This query will return all the values where the time is within 5 minutes after noon, or the highest value before noon.


 
 SELECT *
 FROM Table
 JOIN (SELECT IdColumn, convert(varchar(10), YourColumn, 101) DateColumn, max(YourColumn)
       FROM Table
       WHERE CONVERT(VARCHAR(10), YourColumn, 108)  > '12:05:00 PM'
       GROUP BY idColumn, convert(varchar(10), YourColumn, 101)
 ) t1 ON TAble.IdColumn = t1.IdColumn


This query will be very slow to run as you will not be able to use an index to search the table. An index scan or table scan will be required because you are doing a convert against the column in the table.

This example assumes that the date and time are stored in a single field.
  • AddThis Social Bookmark Button

Browse more Questions and Answers on Development.

Looking for relevant Development Whitepapers? Visit the SearchWinDevelopment.com Research Library.


RSS - Discussions Help

Discuss This Answer


You must be logged-in to discuss a question. Log-in/Register

Mrdenny  |   Apr 17 2008  7:40PM GMT

Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.

 

C Pete  |   Apr 17 2008  7:59PM GMT

Thanks, Mrdenny, but that’s not quite there yet. The situation is that the government NOAA has a database somewhere in the universe that logs data from hundreds of stations all over the world at random intervals. There may be one or ten entries for temperatures between 11:30 am and 12:30 pm on any particular day. What I need is to ask for the records closest to 12:00 pm for every day for the last several years. I’ll then be able to use Excel to figure an average noontime temperature for whatever calendar period. My problem is that the date and time data is stored as one number so I can’t just say “WHERE time between hh:mm:ss AND hh:mm:ss. It doesn’t seem to be able to figure the time without the date too and I can’t type in a date for each day in the decade. I forgot to mention, this isn’t a programming task; it’s just a manual query type in from the keyboard whenever someone asks for the info - so no programming examples, please!

 

Mrdenny  |   Apr 18 2008  6:39PM GMT

Any query examples would be able to be used as one off queries to dump the data so it can be loaded into excel.

The only way to get this data out will be with a bit of SQL code.

I assume that the database is running SQL Server? If it’s running a different database platform that will change the query a bit.