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 13 of 13

Thread: timestamp

  1. #1
    Regular Coder
    Join Date
    Sep 2007
    Posts
    809
    Thanks
    5
    Thanked 2 Times in 2 Posts

    timestamp

    Hi all,

    I have a field in a database called:

    post_time... i didn't set the db up but I would assume that this is the time of the post.

    I want to call the time back out but when I use:

    PHP Code:
    $row['post_time']; 
    it displays - 1254786950, which is the value in the database.

    Do I need to add something before to
    PHP Code:
    $row['post_time']; 
    to get it to display correctly, such as:

    PHP Code:
    timestamp($row['post_time']; 
    Last edited by greens85; 10-15-2009 at 10:46 AM.

  • #2
    Regular Coder
    Join Date
    Mar 2006
    Posts
    238
    Thanks
    3
    Thanked 37 Times in 37 Posts
    Which type exactly has the field post_time in the database ? Are you sure it is TIMESTAMP, not e.g. INT ?

    If it is TIMESTAMP or DATETIME, you could use the MySQL function DATE_FORMAT() to format the date/time in any way you would like to when extracted from the database right in the SQL query.

  • #3
    Regular Coder
    Join Date
    Sep 2007
    Posts
    809
    Thanks
    5
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by SKDevelopment View Post
    Which type exactly has the field post_time in the database ? Are you sure it is TIMESTAMP, not e.g. INT ?

    If it is TIMESTAMP or DATETIME, you could use the MySQL function DATE_FORMAT() to format the date/time in any way you would like to when extracted from the database right in the SQL query.
    Hi SK,

    It has an int value, can I still manipulate this with php to display it as a readable time to the visitors of the site?

  • #4
    Regular Coder
    Join Date
    Jun 2007
    Posts
    310
    Thanks
    86
    Thanked 3 Times in 3 Posts
    Try this - may work - not sure as I'm not totally sure if I understand:

    PHP Code:
    <?php 
    $old_date 
    1254786950
    $new_date date('Y-m-d H:i:s'$old_date);
    print 
    $new_date

    ?>
    To take the time only just delete the Y-m-d bit if you don't want the date

  • #5
    Regular Coder
    Join Date
    Sep 2007
    Posts
    809
    Thanks
    5
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by stfc_boy View Post
    Try this - may work - not sure as I'm not totally sure if I understand:

    PHP Code:
    <?php 
    $old_date 
    1254786950
    $new_date date('Y-m-d H:i:s'$old_date);
    print 
    $new_date

    ?>
    To take the time only just delete the Y-m-d bit if you don't want the date
    Hey stfc,

    It does print a time but its the wrong one... also it wouldnt be suitable as im content will always be changing. To give you more of an idea...

    I currently run a forum, as well as a number of teaching related websites.. what I'm doing is displaying the latest post on the related website, which works fine... however I also want to display the time & date the post was made...

    www.educationvacancies.com/jobseekers.php

    near the bottom you will see what I mean.

  • #6
    Regular Coder
    Join Date
    Mar 2006
    Posts
    238
    Thanks
    3
    Thanked 37 Times in 37 Posts
    I am afraid it is best to use the suitable date/time type for the MySQL post_time field. MySQL has pretty nice date/time functions. It would be not really wise not to use them.

    Yes, you could use INT for storing timestamp (please see the correct answer by Stfc_boy). But I would really recommend against it. I think you would save time for future work with the system if you create one more field of the appropriate type, write the proper values from post_time to that field and keep the old post_time as a backup for a while. In all the future operations you could use the new field.

  • #7
    Regular Coder
    Join Date
    Sep 2007
    Posts
    809
    Thanks
    5
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by SKDevelopment View Post
    I am afraid it is best to use the suitable date/time type for the MySQL post_time field. MySQL has pretty nice date/time functions. It would be not really wise not to use them.

    Yes, you could use INT for storing timestamp (please see the correct answer by Stfc_boy). But I would really recommend against it. I think you would save time for future work with the system if you create one more field of the appropriate type, write the proper values from post_time to that field and keep the old post_time as a backup for a while. In all the future operations you could use the new field.
    Hey SK,

    Unfortunatley my knowledge is no where near deep enough to understand this, as I say I didnt set the database up & have no idea how to start altering it... I just thought their my be a work around with the current database.

  • #8
    Regular Coder
    Join Date
    Mar 2006
    Posts
    238
    Thanks
    3
    Thanked 37 Times in 37 Posts
    in your case it could be something like this:
    Code:
    ALTER TABLE mytable ADD COLUMN post_time1 TIMESTAMP NOT NULL DEFAULT 0;
    
    UPDATE mytable SET post_time1=FROM_UNIXTIME(post_time)+0
    
    SELECT post_time1 FROM mytable;
    the 1st line adds one more field post_time1 to the table.

    The 2nd line fills in the new field. "+0" at the 2nd line is required for compatibility with older versions of MySQL. Also please see this article for reference.

    The 3rd line is added just for check. You could not use ths line at all if it is not necessary ... Or you could add e.g. LIMIT 10 to it not to extract too many lines (if you have many rows already in the table).
    Last edited by SKDevelopment; 10-14-2009 at 06:17 PM. Reason: added some text

  • #9
    Regular Coder
    Join Date
    Sep 2007
    Posts
    809
    Thanks
    5
    Thanked 2 Times in 2 Posts
    Hey SK that worked perfectly, many thanks...

    Just one more (hopefully quick) question:

    The date displays in the format:

    yyyy/mm/dd however as this is an English website, would it be at all possible to display this as dd/mm/yyyy?

    Im currently dragging the data out with this line of php:

    PHP Code:
    Posted on: <?php echo $row['post_time1'];?>
    Once again many thanks
    Last edited by greens85; 10-15-2009 at 10:05 AM.

  • #10
    Regular Coder
    Join Date
    Sep 2007
    Posts
    809
    Thanks
    5
    Thanked 2 Times in 2 Posts
    Got this solved now, for anybody who may need it, i used to following:

    PHP Code:
    $date = $row['post_time1'];
    Posted on: <?php echo date('l M j, Y - h:i A'strtotime($date));?>
    Which will return the date in the following format (todays date as an example)...

    Thursday Oct 15, 2009 - 10:45 AM

  • #11
    Regular Coder
    Join Date
    Mar 2006
    Posts
    238
    Thanks
    3
    Thanked 37 Times in 37 Posts
    I am very glad you have solved the problem. But please notice that strtotime() in this case could be a little bit an overkill. Please notice that you could format your date directly in your query:
    Code:
    select DATE_FORMAT(post_time1,'%W %b %e, - %Y %h:%i %p') as post_time1 FROM mytable WHERE <some_where_condition>;
    If you have problem with character encoding (a hexadecimal number is returned instead of a string), then you could use this:
    Code:
    select CAST(DATE_FORMAT(post_time1,'%W %b %e, - %Y %h:%i %p') AS CHAR) as post_time1 FROM mytable WHERE <some_where_condition>;
    (I made exactly the same formatting here as you did in your most recent post)
    Last edited by SKDevelopment; 10-15-2009 at 01:18 PM.

  • #12
    Regular Coder
    Join Date
    Sep 2007
    Posts
    809
    Thanks
    5
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by SKDevelopment View Post
    I am very glad you have solved the problem. But please notice that strtotime() in this case could be a little bit an overkill. Please notice that you could format your date directly in your query:
    Code:
    select DATE_FORMAT(post_time1,'%W %b %e, - %Y %h:%i %p') as post_time1 FROM mytable WHERE <some_where_condition>;
    If you have problem with character encoding (a hexadecimal number is returned instead of a string), then you could use this:
    Code:
    select CAST(DATE_FORMAT(post_time1,'%W %b %e, - %Y %h:%i %p') AS CHAR) as post_time1 FROM mytable WHERE <some_where_condition>;
    (I made exactly the same formatting here as you did in your most recent post)
    Hey SK,

    Ive recently noticed a problem with the time & date... all the previous ones were converted fine... however if a new post is added it displays in the database as:

    0000-00-00 00:00:00

    Is there anything I can do, to convert it automatically when a new post is added?

  • #13
    Regular Coder
    Join Date
    Mar 2006
    Posts
    238
    Thanks
    3
    Thanked 37 Times in 37 Posts
    When you add a new post, use NOW() to insert the current date/time to your field like
    Code:
    INSERT INTO mytable (field1,field2,filed3,post_time1) VALUES ('a','bc',2,NOW());
    Or use TIMESTAMP column for which the default value is CURRENT_TIMESTAMP. In your table declaration such a field could be declared like:
    Code:
    post_time1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    Please notice you could have only 1 such TIMESTAMP column in a field. Please also notice that MySQL will automatically update value (with the current timestamp) in this column each time you update any field in the table row.

    If this not suitable, please use NOW() on insertion as described above.

    Edit: personally I would use NOW(). This is an insertion date, not last edit date, right? So most probably you do not need the date to change each time the record is edited. I would simply use NOW() on insertion.
    Last edited by SKDevelopment; 10-21-2009 at 03:03 PM.


  •  

    Posting Permissions

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