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.
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?
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...
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.
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.
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).
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)
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?
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.
Before you post, read our: 
