October 12th, 2012, 10:11 AM
Query repeat int_value for each hour until int_value changed
ID:1 Int_Value:23 timestamp:1349961140622
ID:2 Int_Value:34 timestamp:1350035047861
ID:3 Int_Value:12 timestamp:1350043447929
I need two things. First I need to know how to turn timestamp into date/time and then into hours.
Most importantly, I need to know how to produce a row in the result for each hour and have the Int_Value repeated for each hour until the the hour equal to the next time stamp has a different Int_value... then repeat until Int_Value changes again for the date range given.
I have no idea how to do this using SQL. Please help. Result would look like. (of course the timestamps above don't equal the example hours below)
ID Value Hour
1 23 01
2 23 02
3 23 03
4 23 04
5 34 05
6 34 06
7 34 07
8 34 08
9 34 09
10 12 10
11 12 11
12 12 12
13 12 13
Any help or references is greatly appreciated. Thank you for your time.
Note: This timestamp is a bigint and it is representing a Java time stamp...
October 16th, 2012, 10:30 AM
The timestamp is actuall unix time so this is taken care of with
DATEADD() function to figure out the timestamp.
SELECT DATEADD(s,t_stamp/1000,'1970-01-01 00:00:00') FROM sqlth_data...
October 16th, 2012, 01:16 PM
oh shoot, that's it! divide by a thousand!
your numbers looked familiar, but i saw them as way too big to be unix times
thanks for posting and updating the solution
Comments on this post
October 16th, 2012, 02:16 PM
But what I need is still unknown. I am sure it is easy....
Originally Posted by r937
How to I get the repeat or cloak of the same number for every iteration until the int_value changes. A record is only recorded when a value changes. I am trying to produce a result that has a value for ever time iteration. Most would not actually have a record in the db for that time, unless the int_value had changed at that time.
October 16th, 2012, 04:39 PM
i understand this
Originally Posted by stinkyjak
what you need is a LEFT OUTER JOIN where the time iterations are the left table, and your data is the right table, and for each time iteration, there may or may not be a data row