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
    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 06:08 PM.

  • #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
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,025
    Thanks
    2
    Thanked 314 Times in 306 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)"
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

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

  • #6
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,025
    Thanks
    2
    Thanked 314 Times in 306 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)"
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #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
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,025
    Thanks
    2
    Thanked 314 Times in 306 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?
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #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
    •