SitePoint Sponsor

User Tag List

Results 1 to 13 of 13

Thread: time stamp

  1. #1
    SitePoint Addict
    Join Date
    Jun 2008
    Posts
    205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    time stamp

    Code:
    When I query the timestamp 
    | 2008-08-08 06:40:01 |
    | 2008-08-08 06:50:01 |
    | 2008-08-08 07:00:02 |
    | 2008-08-08 07:10:01 |
    | 2008-08-08 07:20:01 |
    | 2008-08-08 07:30:02 |
    | 2008-08-08 07:40:01 |
    | 2008-08-08 07:50:01 |
    | 2008-08-08 08:00:02 |
    | 2008-08-08 08:10:02 |
    | 2008-08-08 08:20:05 |
    | 2008-08-08 08:30:01 |
    | 2008-08-08 08:40:01 |
    | 2008-08-08 08:50:05 |
    | 2008-08-08 09:00:02 |
    | 2008-08-08 09:10:01 |
    | 2008-08-08 09:20:01 |
    | 2008-08-08 09:30:01 |
    | 2008-08-08 09:40:02 |
    | 2008-08-08 09:50:01 |
    | 2008-08-08 10:00:01 |
    | 2008-08-08 10:10:01 |
    +---------------------+
    
    But previous date timestamp may differ with the seconds
     2008-08-07 09:10:01 |
    | 2008-08-07 09:20:01 |
    | 2008-08-07 09:30:01 |
    | 2008-08-07 09:40:01 |
    | 2008-08-07 09:50:01 |
    | 2008-08-07 10:00:01 |
    | 2008-08-07 10:10:01 |
    | 2008-08-07 10:20:01 |
    | 2008-08-07 10:30:01 |
    | 2008-08-07 10:40:01 |
    | 2008-08-07 10:50:01 |
    | 2008-08-07 11:00:02 |
    | 2008-08-07 11:10:01 |
    | 2008-08-07 11:20:01 |
    | 2008-08-07 11:30:01 |
    | 2008-08-07 11:40:01 |
    | 2008-08-07 11:50:02 |
    | 2008-08-07 12:00:01 |
    | 2008-08-07 12:10:01 |
    | 2008-08-07 12:20:01 |
    But When I use this timestamp to query the data from table,
    Due to the seconds mistach the first value is missed out.
    So how can i avoid it using the query excluding the seconds
    If i use timestamp as 2008-08-07 12:20 then last value is missed out. It is considered as 2008-08-07 12:20:00

    select timestamp, fieldname from table where timestamp <= ('".$time."') and timestamp >= date_sub('".$time."',interval 1 day) group by timestamp order by timestamp.

  2. #2
    SitePoint Member
    Join Date
    Aug 2008
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You could try working from something like this which will trim the characters off the tail of the string - 1 is the starting point & 16 is the amount of characters to include in the string. You should be able to customise this to work so you're basically ignoring the seconds.


    SELECT TOP (10)
    SUBSTRING(timestamp, 1, 16) AS TimestampTrimmed
    FROM yourtable


    Julian Parkinson

  3. #3
    SitePoint Addict
    Join Date
    Jun 2008
    Posts
    205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Actually if I use date(timestamp)=date(now()) is for matching the date and current date. In similar way how to match only the format YYYY-mm-dd hr:min
    and not seconds.

    If I use the query as below
    SELECT timestamp from tablename where substring(timestamp,1,16) <= ('2008-08-18 11:20') and substring(timestamp,1,16) >= date_sub('2008-08-18 11:20',interval 1 day) group by timestamp order by timestamp;

    The output doesnot fetch the previous day's data at timestamp 2008-08-18 11:20 even if it present.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    try two changes to your sql --

    1. don't use SUBSTRING on the timestamp
    2. don't use GROUP BY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Jun 2008
    Posts
    205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can you please tell me how can I fetch data from one timestamp to previous timestamp but not including the seconds. I should not compare the seconds in timestamp

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    in post #1 you give some sample data, yes?

    can you show the results you want from that sample data?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Jun 2008
    Posts
    205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    When I query the timestamp 
    | 2008-08-08 06:40:01 |
    | 2008-08-08 06:50:01 |
    | 2008-08-08 07:00:02 |
    | 2008-08-08 07:10:01 |
    | 2008-08-08 07:20:01 |
    | 2008-08-08 07:30:02 |
    | 2008-08-08 07:40:01 |
    | 2008-08-08 07:50:01 |
    | 2008-08-08 08:00:02 |
    | 2008-08-08 08:10:02 |
    | 2008-08-08 08:20:05 |
    | 2008-08-08 08:30:01 |
    | 2008-08-08 08:40:01 |
    | 2008-08-08 08:50:05 |
    | 2008-08-08 09:00:02 |
    | 2008-08-08 09:10:01 |
    | 2008-08-08 09:20:01 |
    | 2008-08-08 09:30:01 |
    | 2008-08-08 09:40:02 |
    | 2008-08-08 09:50:01 |
    | 2008-08-08 10:00:01 |
    | 2008-08-08 10:10:02 |
    +---------------------+
    This is the sample data where $time='2008-08-08 10:10:02' and if the previous date time is 2008-08-07 10:10:01, then this 2008-08-07 10:10:01 is missed out.


    The database is updated for every ten minutes. To get the exact one day difference due to seconds the row is missed out.

    select timestamp, name from table where timestamp <= ('".$time."') and timestamp >= date_sub('".$time."',interval 1 day) order by timestamp

    Is there any way to find the timestamp excluding seconds or how to use the clause timestamp exlcuding the seconds.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i'm sorry, i still do not understand what you're looking for
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict
    Join Date
    Jun 2008
    Posts
    205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i'm sorry, i still do not understand what you're looking for
    Hi,

    Suppose timestamp is 2008-08-19 11:00:01 .
    How can i use clause where timestamp like 2008-08-19 11:00% and find the one day interval data by using date_sub(timestamp, interval 1 day)

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i don't understand why you cannot generate the specific timestamps you need

    you said you're using this --
    Code:
    select timestamp, name from table 
    where timestamp <= ('".$time."') 
    and timestamp >= date_sub('".$time."',interval 1 day) 
    order by timestamp
    perhaps you should be using this --
    Code:
    select timestamp, name from table 
    where timestamp >= '".$starttime."' 
    and timestamp <= '".$endtime."' 
    order by timestamp
    that way, you can do the "plus or minus 60 seconds" calculation in php, where you have control over the exact range you want to return
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Addict
    Join Date
    Jun 2008
    Posts
    205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i don't understand why you cannot generate the specific timestamps you need

    you said you're using this --
    Code:
    select timestamp, name from table 
    where timestamp <= ('".$time."') 
    and timestamp >= date_sub('".$time."',interval 1 day) 
    order by timestamp
    perhaps you should be using this --
    Code:
    select timestamp, name from table 
    where timestamp >= '".$starttime."' 
    and timestamp <= '".$endtime."' 
    order by timestamp
    that way, you can do the "plus or minus 60 seconds" calculation in php, where you have control over the exact range you want to return
    when date(timestamp)=date(now()) is used it matches the date of timestamp and current date.

    Similar way is there any format which which matches yyyy-dd-mm hr:min

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by sandy1028 View Post
    Similar way is there any format which which matches yyyy-dd-mm hr:min
    yes, there is, although using it in a WHERE clause will not be efficient -- you should use a range test instead

    DATE_FORMAT(column,'%Y-%m-%d %H:%i')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Addict
    Join Date
    Jun 2008
    Posts
    205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have to select $time from one table.

    When i query for 1 day interval as above I have to get exact 144 rows.
    But due to seconds mismatch the previous day's first value is missed out.

    Please tell me some solution to this


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •