troubleshooting Question

SQL for top of the hour data

Avatar of browneye9000
browneye9000 asked on
Databases
16 Comments1 Solution2791 ViewsLast Modified:
Disclaimer:  I am an SQL idiot.  Everything I have done is copy and paste with the help of others.

I am currently using SQL to query a database located in one application called InfoPlus21 ( a process control data historian) to another application called Northwest Analytical (a statistical process control application).  Someone told me that I need to say what the DBMS is but I don't know.

This is the code I have so far.

SELECT ts, avg  FROM aggregate  WHERE name='T2EG:AE143201.PV'  AND ts > getdbtime-500:00:00.0  AND period = 1:00

The ts stands for time stamp.  The problem is that when I run the query the data is pulled starting with the getdbtime (I guess this is the current time) and then goes back for 500 1 hr intervals.

For reasons that are much too detailed to write here I need for the first time stamp to be started at the 00:00:00.0 time.  I gues this may be achieved by taking the getdbtime and then going back to the first occurance of 00:00:00.0 but I'm not sure how to do this.

Please feel free to ask questions if you need more clarification but keep my disclaimer in mind.
ASKER CERTIFIED SOLUTION
cmleung2

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 16 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 16 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros