Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9

Thread: Time and date

  1. #1
    Regular Coder
    Join Date
    Jul 2005
    Posts
    153
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Time and date

    Whats the best way to use date and time in mysql database, It will need to be easy to work with, for example i need to be able to subtract days.

    Thank You

  2. #2
    Regular Coder
    Join Date
    Jul 2005
    Posts
    153
    Thanks
    1
    Thanked 0 Times in 0 Posts
    What im trying to do is make a page that shows my inactive users. So i need the best way to see if the user has not been online for 30days.
    Last edited by simjay; 09-07-2008 at 07:08 PM.

  3. #3
    Regular Coder
    Join Date
    Jul 2005
    Posts
    153
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I am sort of getting some where now, I want to see if the user has been inactive for 30 days or longer. I have this...

    PHP Code:
    // $timestamp = 1220895924 gets this from the DB.

    <?
    $last 
    date("d.m.y",$timestamp);

      if (
    time() - $timestamp  > (30*24*60*60))
     {
    echo 
    "inactive";
    }

    ?>

  4. #4
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,886
    Thanks
    3
    Thanked 456 Times in 446 Posts
    If you use a mysql DATETIME data type, you can execute a simple query to return rows more than 30 days ago -

    PHP Code:
    $query "SELECT * FROM table_name WHERE date_time_column < DATE_SUB(NOW(),INTERVAL 30 DAY)"
    Finding out HOW to do something is called research, i.e. keep searching until you find the answer. After you attempt to do something and cannot solve a problem with it yourself, would be when you ask others for help.

  5. #5
    Regular Coder
    Join Date
    Jul 2005
    Posts
    153
    Thanks
    1
    Thanked 0 Times in 0 Posts
    No im adding it as time()

  6. #6
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,886
    Thanks
    3
    Thanked 456 Times in 446 Posts
    A Unix timestamp is the hardest way of storing date/time information as it requires a slow conversion to be usable for almost everything.

    A query that does the same as above using a Unix timestamp column -
    PHP Code:
    $query "SELECT * FROM table_name WHERE FROM_UNIXTIME(unix_timestamp_column) < DATE_SUB(NOW(),INTERVAL 30 DAY)"
    Finding out HOW to do something is called research, i.e. keep searching until you find the answer. After you attempt to do something and cannot solve a problem with it yourself, would be when you ask others for help.

  7. #7
    Regular Coder
    Join Date
    Jul 2005
    Posts
    153
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I need it to be in a IF statment, is there no way i can just use


    PHP Code:
    if ($timestamp  > (30*24*60*60)) 

  8. #8
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,886
    Thanks
    3
    Thanked 456 Times in 446 Posts
    Why would you want to use some slow parsed/tokenized/interpreted php code when the query already returns the rows that match your comparison?
    Finding out HOW to do something is called research, i.e. keep searching until you find the answer. After you attempt to do something and cannot solve a problem with it yourself, would be when you ask others for help.

  9. #9
    Regular Coder
    Join Date
    Jul 2005
    Posts
    153
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Because I will not just be using 30 days, I have other queries to do on the same page.


 

Posting Permissions

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