[thelist] [MySQL] - converting timetsamp values in php

Peter Lowe pgl at instinct.org
Sun Aug 3 19:50:25 CDT 2003


On Aug 04, Paul Bennett wrote:
> I have decided (from Rudy's suggestions) to start letting the database 
> deal with timestamping, rather than setting the value using the 
> strtotime() function.

What string were you using to set the timestamp? Isn't using time()
easier? :)

> I now have a field in one of my tables which logs the created time as  
> TIMESTAMP value (YYYYMMDDHHMMSS).
> My questions:
> - is there a built in function (PHP) to output this time as more human 
> readable (DD MM YYYY, HH:MM)?

Two methods:

 1) In MySQL, use this:

 	select unix_timestasmp(datefield) from yourtable;

    then in PHP, do something like this:

    	<?=date('d m Y, H:i')?>

    (Please check that date format at http://php.net/date - I normally
    have to refer to that page to get it right.)

 2) In MySQL, use this:

	select date_format(datefield, '%d %m %Y, %H:%i') from yourtable;

    and just echo it in PHP.

> I have searched the manual but to no avail. I can roll my own, but would 
> rather not if something is available.
> 
> I am also aware of the fact that the database itself can switch formats 
> as it pulls out records - are there any caveats to this method?

Using the unix_timestamp() function in MySQL has caused confusion for me
in the past whe the field is set to show YYYYMMDDHHMM (ie, without the
seconds displayed). The timestamp is recorded in MySQL to the second,
which gave some weird rounding errors.

> would it 
> be the preferred method?

MySQL has some pretty cool date / time functions, but personally I've
always used int fields (set to whatever time() returns in PHP) to store
unix timestamp - I normally end up having to use them when converting
dates anyway, and I find it easier to stick to one set of functions.

cheers,

Peter

-- 
The Czech Republic: Home of the world's finest beer.
Litres drunk by Czechs so far this year: 966,665,467.20

 - http://prague.tv/toys/beer/


More information about the thelist mailing list