browneye9000
asked on
SQL for top of the hour data
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.
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
I ran this query at 8:44 pm. You can see that the data starts at 20:44:29.0 and goes back in 1 hr increments which is expected with the code I have.
I would like to have the query pull the first available top of the hour data and go back (20:00, 19:00, 18:00, etc.) Not in reverse order mind you just in this order but starting at the first available top of the hour.
13-DEC-06_10:44:29.9 76.79
13-DEC-06_11:44:29.9 80.07
13-DEC-06_12:44:29.9 81.60
13-DEC-06_13:44:29.9 81.45
13-DEC-06_14:44:29.9 78.23
13-DEC-06_15:44:29.9 72.74
13-DEC-06_16:44:29.9 75.72
13-DEC-06_17:44:29.9 83.02
13-DEC-06_18:44:29.9 86.34
13-DEC-06_19:44:29.9 79.27
13-DEC-06_20:44:29.9 80.99
I would like to have the query pull the first available top of the hour data and go back (20:00, 19:00, 18:00, etc.) Not in reverse order mind you just in this order but starting at the first available top of the hour.
13-DEC-06_10:44:29.9 76.79
13-DEC-06_11:44:29.9 80.07
13-DEC-06_12:44:29.9 81.60
13-DEC-06_13:44:29.9 81.45
13-DEC-06_14:44:29.9 78.23
13-DEC-06_15:44:29.9 72.74
13-DEC-06_16:44:29.9 75.72
13-DEC-06_17:44:29.9 83.02
13-DEC-06_18:44:29.9 86.34
13-DEC-06_19:44:29.9 79.27
13-DEC-06_20:44:29.9 80.99
browneye9000,
In SQL Server it would go like this:
DECLARE @tophour datetime
SET @tophour = DATEADD(hh, HOUR(GETDATE(), CONVERT(datetime, CONVERT(varchar(10), GETDATE(), 101)))
SELECT ts, avg
FROM aggregate
WHERE name='T2EG:AE143201.PV' AND ts > DATEADD(hh, -500, @tophour) AND period = '0:01:00'
Regards,
Patrick
In SQL Server it would go like this:
DECLARE @tophour datetime
SET @tophour = DATEADD(hh, HOUR(GETDATE(), CONVERT(datetime, CONVERT(varchar(10), GETDATE(), 101)))
SELECT ts, avg
FROM aggregate
WHERE name='T2EG:AE143201.PV' AND ts > DATEADD(hh, -500, @tophour) AND period = '0:01:00'
Regards,
Patrick
Are you wanting to just have it show the hour grouped together without the min sec.. if so just use
SELECT HOUR(ts) as tsh, avg FROM aggregate WHERE name='T2EG:AE143201.PV' AND ts > getdbtime-500:00:00.0 AND period = 1:00
SELECT HOUR(ts) as tsh, avg FROM aggregate WHERE name='T2EG:AE143201.PV' AND ts > getdbtime-500:00:00.0 AND period = 1:00
ASKER
I have tried both of these and neither works. I must have some other kind of limitations.
Both solutions should work, what error message did you get?
ASKER
"Couldn't Open External Database. Error (3146)
ODBC -- call failed.
ODBC -- call failed.
Try this:
SELECT ts, avg
FROM aggregate
WHERE name='T2EG:AE143201.PV'
AND ts > DATEADD(hh, -500, DATEADD(hh, HOUR(GETDATE(), CONVERT(datetime, CONVERT(varchar(10), GETDATE(), 101))))
AND period = '0:01:00'
SELECT ts, avg
FROM aggregate
WHERE name='T2EG:AE143201.PV'
AND ts > DATEADD(hh, -500, DATEADD(hh, HOUR(GETDATE(), CONVERT(datetime, CONVERT(varchar(10), GETDATE(), 101))))
AND period = '0:01:00'
ASKER
That didn't work either. I keep getting the same error. I think that by the way the SQL is getting sent to the db that the SQL has to be very basic. Thanks for trying. It may have to be so basic that I won't be able to do this.
ASKER
I tried this in a program called SQL Plus. I used this program to see what error may have occured. The error says that HOUR is a reserved word.
oh, sql plus is for oracle database, i was assuming you were using microsoft sql server. In this case the sql syntax and the calcultion will be totally different. I don't have sql plus with me until tomorrow morning, so I can't really test this thing out for you. But give it a shot and see how it goes:
SELECT ts, avg
FROM aggregate
WHERE name='T2EG:AE143201.PV'
AND ts > trunc(sysdate) - (500/24)
AND period = 1
Note: trunc(systdate), sysdate is the system date, which maybe the same as getdbtime in your case, and trunc because set the time to midnight, which should satisfy your 00:00:00 requirement. You can find more information in http://www.orafusion.com/art_dt.htm and http://www.psoug.org/reference/date_func.html, if they are not too intimidating for you.
SELECT ts, avg
FROM aggregate
WHERE name='T2EG:AE143201.PV'
AND ts > trunc(sysdate) - (500/24)
AND period = 1
Note: trunc(systdate), sysdate is the system date, which maybe the same as getdbtime in your case, and trunc because set the time to midnight, which should satisfy your 00:00:00 requirement. You can find more information in http://www.orafusion.com/art_dt.htm and http://www.psoug.org/reference/date_func.html, if they are not too intimidating for you.
I just tried it out, the above query should work find in sql plus.
ASKER
I posted something last night but for some reason it didn't come through.
It didn't work. I got the error "Column or variable "sysdate" not found at line 4.
I changed sysdate to getdbtime and I am now getting the error "Expecting "REAL" expression, found "TIME_STAMP" at line 4."
This is the way it is now.
SELECT ts, avg
FROM aggregate
WHERE name='T2EG:AE143201.PV'
AND ts > trunc(getdbtime) - (500/24)
AND period = 1
I read this in the SQL Plus help file.
The following table describes the data types that SQL uses, and their corresponding data types in the InfoPlus.21 database.
SQL Data Type InfoPlus.21 Data Types
CHARACTER Character
BIT Scratch Pad
INTEGER Integer
REAL Real
TIMESTAMP Timestamp
RECORD Record (length 2)
FIELD Record (length 6)
I think my problem is that SQLPlus is being used to query data in InfoPlus.21 and there are restrictions.
I also read this in the help file about TRUNC.
TRUNC truncates a value to a number of decimal places.
Example:
TRUNC(3.141592643, 3) = 3.141
TRUNC(3.141592643) = 3
TRUNC(123456789, -2) = 123456700
So I guess TRUNC can only be used in IP21 for values and not varables?
You have helped a lot so far. Please don't give up on me.
It didn't work. I got the error "Column or variable "sysdate" not found at line 4.
I changed sysdate to getdbtime and I am now getting the error "Expecting "REAL" expression, found "TIME_STAMP" at line 4."
This is the way it is now.
SELECT ts, avg
FROM aggregate
WHERE name='T2EG:AE143201.PV'
AND ts > trunc(getdbtime) - (500/24)
AND period = 1
I read this in the SQL Plus help file.
The following table describes the data types that SQL uses, and their corresponding data types in the InfoPlus.21 database.
SQL Data Type InfoPlus.21 Data Types
CHARACTER Character
BIT Scratch Pad
INTEGER Integer
REAL Real
TIMESTAMP Timestamp
RECORD Record (length 2)
FIELD Record (length 6)
I think my problem is that SQLPlus is being used to query data in InfoPlus.21 and there are restrictions.
I also read this in the help file about TRUNC.
TRUNC truncates a value to a number of decimal places.
Example:
TRUNC(3.141592643, 3) = 3.141
TRUNC(3.141592643) = 3
TRUNC(123456789, -2) = 123456700
So I guess TRUNC can only be used in IP21 for values and not varables?
You have helped a lot so far. Please don't give up on me.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
oops, sorry, that wouldn't work neither, getdbtime-getdbtime doesn't make sense, sorry.
ASKER
Thanks for trying. I gonna keep working on this.
Can you take another crack at describing the problem?
:)
Regards,
Patrick