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.
Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Regular Coder
    Join Date
    Oct 2005
    Location
    Surrey, England
    Posts
    286
    Thanks
    2
    Thanked 2 Times in 2 Posts

    MySQL > PHP Timestamp

    ok heres the problem, i have made a MySQL Timestamp through phpMyAdmin and now im stuck, i know how to echo this onto my page but how do i customise it so its not just a bunch of turd?

  • #2
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Use MySQL's DATE_FORMAT() function.

  • #3
    Regular Coder
    Join Date
    Oct 2005
    Location
    Surrey, England
    Posts
    286
    Thanks
    2
    Thanked 2 Times in 2 Posts
    can you help me with this please... i have it stored in

    Code:
    $timestamp;
    i dont understand how to change the numbers into letters etc... the website is kinda confusing

  • #4
    Senior Coder
    Join Date
    Nov 2002
    Location
    North-East, UK
    Posts
    1,265
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Alot of people prefer to use an integer time as it is easy manipulated using PHP's date() function.

    http://php.codenewbie.com/articles/p...mp-Page_1.html

  • #5
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Assuming you want your date to read out like 'January 12, 2006':

    Code:
    SELECT
     DATE_FORMAT(yourdatecolumn,'%M %e, %Y') AS mydate
    FROM
     table
    When you retrieve mydate, it will already be formatted, and you don't have to do any PHP processing. The doc page I linked to earlier lists all the formatting codes you can use.

    I wouldn't suggest storing your dates as a UNIX timestamp. MySQL has a lot of very useful date and time functions that won't work on them. And if you ever really need a unix timestamp for something, there's a function to convert.

  • #6
    Regular Coder
    Join Date
    Oct 2005
    Location
    Surrey, England
    Posts
    286
    Thanks
    2
    Thanked 2 Times in 2 Posts
    ok im confused... is this right? it doesnt display anything

    Code:
      $ts = mysql_query("SELECT DATE_FORMAT(timestamp,'%M %e, %Y') AS mydate FROM news");
      $tsr = mysql_fetch_array($ts);
      $timestamp = $tsr['timestamp'];

  • #7
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    When using functions on selected columns, you should alias the column for ease of retrieval. In the previous code, the formatted date column has been aliased to 'mydate', so that's the string you need to use to retrieve it in PHP.

    PHP Code:
      $ts mysql_query("SELECT DATE_FORMAT(timestamp,'%M %e, %Y') AS mydate FROM news");
      
    $tsr mysql_fetch_array($ts);
      
    $timestamp $tsr['mydate']; 
    Note that you can alias a function's results as the original column name. The following will also work:

    PHP Code:
      $ts mysql_query("SELECT DATE_FORMAT(timestamp,'%M %e, %Y') AS timestamp FROM news");
      
    $tsr mysql_fetch_array($ts);
      
    $timestamp $tsr['timestamp']; 

  • #8
    Regular Coder
    Join Date
    Oct 2005
    Location
    Surrey, England
    Posts
    286
    Thanks
    2
    Thanked 2 Times in 2 Posts
    thanks for the help... just 1 more problem, how do i set my mysql to auto insert the current timestamp when the new row is created?

  • #9
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    What version of MySQL are you using?

  • #10
    Regular Coder
    Join Date
    Oct 2005
    Location
    Surrey, England
    Posts
    286
    Thanks
    2
    Thanked 2 Times in 2 Posts
    im not sure, i just used NOW() and it seemed to work but the time is wrong... how can i change this?

  • #11
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Please run a 'SHOW CREATE TABLE yourtablename' query and post the result. A 'SHOW VERSION()' will help, too.

    [Edit]Oops. SHOW VERSION() should be SELECT VERSION().
    Last edited by Kid Charming; 01-12-2006 at 10:34 PM.

  • #12
    Regular Coder
    Join Date
    Oct 2005
    Location
    Surrey, England
    Posts
    286
    Thanks
    2
    Thanked 2 Times in 2 Posts
    Code:
    CREATE TABLE `news` (
     `id` int(11) NOT NULL auto_increment,
     `title` varchar(200) NOT NULL default '',
     `post` longtext NOT NULL,
     `author` varchar(35) NOT NULL default '',
     `timestamp` timestamp(14) NOT NULL,
     `ip` varchar(80) NOT NULL default '',
     `visible` char(1) NOT NULL default '1',
     PRIMARY KEY  (`id`)
    ) TYPE=ISAM
    i dont know the version but its likley to be new as its paid hosting

  • #13
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Nope, you're on an older version -- probably 4.0, but maybe as low as 3.23. You can find out for sure by running a 'SELECT VERSION()' query (not SHOW, as I typed earlier).

    In either version, your timestamp field will automatically set itself to the server's current time when a row is created or modified; you do not need to specifically set it to NOW(). If that time is wrong, than it's a problem with the server's time (though it may mean the server's in a different timezone).

  • #14
    Regular Coder
    Join Date
    Oct 2005
    Location
    Surrey, England
    Posts
    286
    Thanks
    2
    Thanked 2 Times in 2 Posts
    indeed, my server is located in the USA, im in the UK. is there a way of dragging the '1029482612' number string onto a php document and then editing it so i add like 4 hour's and and x minutes?

  • #15
    Senior Coder
    Join Date
    Nov 2002
    Location
    North-East, UK
    Posts
    1,265
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You can use strtotime to add time
    PHP Code:
    <?php
    $date1 
    1029482612;
    $date2 strtotime("+ 5 hours 30 minutes"$date1);

    echo 
    date("d/m/Y H:i:s"$date2);
    ?>
    http://uk2.php.net/manual/en/function.strtotime.php


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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