Unsure how to approach this problem:

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Unsure how to approach this problem:

Author

Topic

jtwood
Starting Member

2 Posts

Posted - 2009-08-04 : 08:28:18

*I do have some experience with SQL...enough to get by for basic tasks and simple stored procedures, etc*

I have a piece of software that logs data to a database every second.

We use several different levels of detail when viewing the data (ie: 1 hour, 6 hour, 12 hour ---> 1 year)

Of course, putting 30 million or so data points into a chart is not really needed for the year time frame.

I need to figure out some way to select data from every 5 seconds or every 30 seconds, etc... as required by the time frame.

Timestamps are used for keys -

I am using My SQL 5.1 and the table creation statement follows. There is not much room for changing the table layout --- the software that writes to this table also creates it and if it is changed just recreates it.

All help and suggestions are much appreciated.

Thanks in advance,
Justin


DROP TABLE IF EXISTS `clscada`.`sk00trend`;
CREATE TABLE  `clscada`.`sk00trend` (
  `Time_Stamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `Time_Stamp_ms` int(11) NOT NULL DEFAULT '0',
  `ReturnRateWater` double DEFAULT NULL,
  `LPGasRate` double DEFAULT NULL,
  `SC_MODA_CH6` double DEFAULT NULL,
  `SC_MODA_CH7` double DEFAULT NULL,
  `ReturnRateAll` double DEFAULT NULL,
  `ReturnRateOil` double DEFAULT NULL,
  `ReturnStatic` double DEFAULT NULL,
  `Bias` int(11) NOT NULL DEFAULT '0',
  `HPGasRate` double DEFAULT NULL,
  `HPGasStatic` double DEFAULT NULL,
  `LPGasStatic` double DEFAULT NULL,
  `TND_SK00_SC_MODA_CH4` double DEFAULT NULL,
  PRIMARY KEY (`Time_Stamp`,`Time_Stamp_ms`,`Bias`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 PACK_KEYS=1;

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-04 : 08:33:19

This is a Microsoft SQL forum.
Try www.dbforums.com for questions regarding MySQL.

N 56°04'39.26"
E 12°55'05.63"

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-08-04 : 08:40:01

Hi

you have to post here....http://forums.mysql.com/

-------------------------
R..
http://code.msdn.microsoft.com/SQLExamples/
http://msdn.microsoft.com/hi-in/library/bb500155(en-us).aspx

jtwood
Starting Member

2 Posts

Posted - 2009-08-04 : 09:08:18

quote:
Originally posted by rajdaksha

Hi

you have to post here....http://forums.mysql.com/

-------------------------
R..
http://code.msdn.microsoft.com/SQLExamples/
http://msdn.microsoft.com/hi-in/library/bb500155(en-us).aspx

What if I 'pretend' to be using MSSQL just to see if anybody has any idea on how to approach this? Concepts should be platform/software compatible.

I do apologize for missposting. I recently switched over to MySQL from MS and have gotten so used to going through these forums.

Ooops.

Thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-04 : 09:23:28

quote:
Concepts should be platform/software compatible.

You are absolutely right! they SHOULD be! :)

You'll probably get better help on one of the other forums is all.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-04 : 09:23:44

[code]DECLARE @Interval INT

SET @Interval = 3600

SELECT DATEADD(SECOND, DATEDIFF(SECOND, '20090101', Time_Stamp) / @Interval * @Interval), '20090101') AS theTime,
SUM(SC_MODA_CH6) AS theSum
FROM clscada.sk00trend
GROUP BY DATEADD(SECOND, DATEDIFF(SECOND, '20090101', Time_Stamp) / @Interval * @Interval), '20090101')
ORDER BY DATEADD(SECOND, DATEDIFF(SECOND, '20090101', Time_Stamp) / @Interval * @Interval), '20090101')[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources