Page 1 of 1
converting DATE to a more readable format
Posted: Mon Apr 19, 2004 3:28 pm
by forgodsake8
Hi, ive got a field called date that is automatically inserted into my table.
When i output the contents I get this
2004-04-19 18:59:55.465774
How do I get this into a friendlier format, for example
Tuesday 19th April 6.59pm
any ideas???
Thanks
lou
Posted: Mon Apr 19, 2004 3:35 pm
by JAM
If you select the field using UNIX_TIMESTAMP you can easely create whatever you prefer:
Code: Select all
select unix_timestamp(thedatefield) from table
Code: Select all
<?php
$timestamp = '1082406673'; // got it by using 'select unix_timestamp(now())' in my database
echo date("l dS F h:iA", $timestamp)
?>
Result:
Posted: Mon Apr 19, 2004 4:00 pm
by forgodsake8
Hi,
I'm not sure how im meant to use the
select unix_timestamp(thedatefield) from table on my page?
do I use it on the php page or in the database???
I have already inserted a date for each field, using 'now', so everytime something is inserted into the database the date it was entered is also saved.
I've used what you suggested below in the PHP page, but it keeps returning the wrong date in the timestamp, it says is it 1.33AM ...which is wrong.
Any suggestions?
thanks.
Posted: Mon Apr 19, 2004 4:05 pm
by JAM
I'm recommending using unix_timestamp in the SQL query itself. There are other ways to do this, but this is what I prefer myself. This is just pseudo code to explain further:
Code: Select all
<?php
// db connection and more here...
$result = mysql_query("select foo, bar, unix_timestamp(mydatefield) from table where foo = 'muppets!'");
while ($row = mysql_fetch_array($result)) {
echo date("l dS F h:iA", $row[2];
}
?>
Hope that made it clearer. If not, let us know.
Posted: Mon Apr 19, 2004 5:46 pm
by forgodsake8
Hi,
the query im using on page, returns * from certain tables:
select * from ts_module_books inner join advert2 on advert2.mod_book_id = ts_module_books.id where ts_module_books.book_title LIKE '%$search%'");
is there another way of doing the date function besides the way you showed above?
Cheers