Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
SQL TimeStamp step
- Thread starter wuxapian
- Start date
I'd do like this, first create a view for your table that returns all columns but the timestamp column. Instead you add a column with a modifed timestamp, which you use when comparing timestamps.
Something like:
CREATE VIEW v1 AS
SELECT c1,
...,
SUBSTRING(CAST(ts AS CHAR(40)) FROM 11 FOR 14) ||
CAST(EXTRACT(MINUTE FROM ts)/15 AS CHAR(1)) AS ts FROM tab;
ts above is the timestamp column.
Note that you may have to modify the SUBSTRING values. In my example I expect the SQL standard's timstamp format:
TIMESTAMP'2004-05-14 14:20:05.930000'
Then use the view to select data 'in steps':
SELECT AVG(c1), ts FROM v1
GROUP BY ts
Something like:
CREATE VIEW v1 AS
SELECT c1,
...,
SUBSTRING(CAST(ts AS CHAR(40)) FROM 11 FOR 14) ||
CAST(EXTRACT(MINUTE FROM ts)/15 AS CHAR(1)) AS ts FROM tab;
ts above is the timestamp column.
Note that you may have to modify the SUBSTRING values. In my example I expect the SQL standard's timstamp format:
TIMESTAMP'2004-05-14 14:20:05.930000'
Then use the view to select data 'in steps':
SELECT AVG(c1), ts FROM v1
GROUP BY ts
- Status