#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2009
    Posts
    8
    Rep Power
    0

    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...
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2009
    Posts
    8
    Rep Power
    0
    The timestamp is actuall unix time so this is taken care of with

    DATEADD() function to figure out the timestamp.
    Usage:

    SELECT DATEADD(s,t_stamp/1000,'1970-01-01 00:00:00') FROM sqlth_data...
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,209
    Rep Power
    4279
    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

    • stinkyjak agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2009
    Posts
    8
    Rep Power
    0
    Originally Posted by r937
    thanks for posting and updating the solution

    But what I need is still unknown. I am sure it is easy....

    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.
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,209
    Rep Power
    4279
    Originally Posted by stinkyjak
    I am trying to produce a result that has a value for ever time iteration.
    i understand this

    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

    correct?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo