SQL Query for time data only

15 pts.
Tags:
SQL
SQL Query
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

<pre>

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

</pre>

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.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.
    66,070 pointsBadges:
    report
  • C Pete
    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!
    15 pointsBadges:
    report
  • Denny Cherry
    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.
    66,070 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following