...

View Full Version : Resolved timestamp



greens85
10-14-2009, 05:20 PM
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:


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

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


timestamp($row['post_time'];

SKDevelopment
10-14-2009, 05:40 PM
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.

greens85
10-14-2009, 05:45 PM
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?

stfc_boy
10-14-2009, 05:48 PM
Try this - may work - not sure as I'm not totally sure if I understand:



<?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

greens85
10-14-2009, 05:55 PM
Try this - may work - not sure as I'm not totally sure if I understand:



<?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.

SKDevelopment
10-14-2009, 06:22 PM
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.

greens85
10-14-2009, 06:52 PM
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.

SKDevelopment
10-14-2009, 07:14 PM
in your case it could be something like this:


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 (http://kitt.hodsden.org/mysql/converting_to_mysqls_timestamp_from_int11) 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).

greens85
10-15-2009, 11:02 AM
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:


Posted on: <?php echo $row['post_time1'];?>

Once again many thanks :thumbsup:

greens85
10-15-2009, 11:45 AM
Got this solved now, for anybody who may need it, i used to following:


$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

SKDevelopment
10-15-2009, 02:14 PM
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:


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:


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)

greens85
10-21-2009, 11:21 AM
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:


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:


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?

SKDevelopment
10-21-2009, 03:55 PM
When you add a new post, use NOW() to insert the current date/time to your field like


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:


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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum