SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru Richi's Avatar
    Join Date
    Jul 2001
    Location
    Hu,Bp.
    Posts
    842
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql, how can i make this select ?

    hi all ..

    I have insert in DB unix timestamp, and i need to select the rows from db where actual time longer with 5 minute , I have tried this but didn`t work :

    Select * form table where lastclick+18000 <= UNIX_TIMESTAMP();

    18000 secend = 5 minute.

    any clew ?!




    thx in advance.

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Isn't 5 minutes == 300 seconds?
    ...where unix_timestamp() - unix_timestamp(lastclick) > 300

    Or this way:
    ...where date_sub(now(), interval 5 minute) > lastclick
    (easier to understand what it means)

    UNIX_TIMESTAMP(date)
    If called with no argument, returns a Unix timestamp (seconds since '1970-01-01 00:00:00' GMT) as an unsigned integer. If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since '1970-01-01 00:00:00' GMT. date may be a DATE string, a DATETIME string, a TIMESTAMP, or a number in the format YYMMDD or YYYYMMDD in local time
    MySQL Reference Manual (C) 2002 MySQL AB

  3. #3
    SitePoint Guru Richi's Avatar
    Join Date
    Jul 2001
    Location
    Hu,Bp.
    Posts
    842
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ohhh ... LOL ...

    Mathamatic error , I tho (60 sec * 60 min * 5 min)

    thx ... koszi haver

  4. #4
    SitePoint Guru Richi's Avatar
    Join Date
    Jul 2001
    Location
    Hu,Bp.
    Posts
    842
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    btw ..
    lastclick value is stimestamp, then this will be error :
    unix_timestamp() - unix_timestamp(lastclick) > 300

    and
    date_sub(now(), interval 5 minute)
    is return normal date, time , not timestamp !

    the right way is :
    unix_timestamp(now()) - lastclick > 300

    thx the idea

  5. #5
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Richi
    btw ..
    lastclick value is stimestamp, then this will be error :
    unix_timestamp() - unix_timestamp(lastclick) > 300
    Are you sure?
    ...If UNIX_TIMESTAMP() is called with a date argument, ... date may be ... a TIMESTAMP, ...

  6. #6
    SitePoint Guru Richi's Avatar
    Join Date
    Jul 2001
    Location
    Hu,Bp.
    Posts
    842
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    with :
    unix_timestamp() - unix_timestamp(lastclick) > 300

    will list all the records !
    and in mysql console i got 0 from :

    select unix_timestamp(1054458732);

    unix_timestamp will convert from normal date to timestamp .. this what i had read it from mysql.com :

    UNIX_TIMESTAMP(date) If called with no argument, returns a Unix timestamp (seconds since '1970-01-01 00:00:00' GMT) as an unsigned integer. If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since '1970-01-01 00:00:00' GMT. date may be a DATE string, a DATETIME string, a TIMESTAMP, or a number in the format YYMMDD or YYYYMMDD in local time: mysql> SELECT UNIX_TIMESTAMP(); -> 882226357mysql> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00'); -> 875996580When UNIX_TIMESTAMP is used on a TIMESTAMP column, the function will return the internal timestamp value directly, with no implicit ``string-to-unix-timestamp'' conversion. If you pass an out-of-range date to UNIX_TIMESTAMP() it will return 0, but please note that only basic checking is performed (year 1970-2037, month 01-12, day 01-31). If you want to subtract UNIX_TIMESTAMP()umns, you may want to cast the result to signed integers. See section 6.3.5 Cast Functions.
    http://www.mysql.com/doc/en/Date_and...functions.html


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
  •