MySQL DATETIME and PHP Dates

Hi,

Is there an easy way of formatting a MySQL DATETIME field in PHP. At the
moment I'm using the code below, as using the returned DATETIME value from
the database with the date() function doesn't seem to work. I know I could
use the MySQL function date_format to format it before its returned but I
use a simply query (e.g. SELECT * FROM TABLE WHERE colum = '$somevalue') and
a while loop to read all the variables in, so I don't think I can format it
before its returned.

$date1 = "2005-04-12 20:45:54";
$date_array = explode("-", $date1);
$year = $date_array[0];
$month = $date_array[1];
$day = $date_array[2];
$day = $day[0].$day[1];
$ts = mktime(0,0,0,$month, $day, $year);
$dateVal = date("j-M-y", $ts);
echo "<br>Date is: ".$dateVal;

Thanks,

T.

re: MySQL DATETIME and PHP Dates

Tony Clarke wrote:[color=blue]
> Is there an easy way of formatting a MySQL DATETIME field in PHP. At[/color]
the[color=blue]
> moment I'm using the code below, as using the returned DATETIME value[/color]
from[color=blue]
> the database with the date() function doesn't seem to work. I know I[/color]
could[color=blue]
> use the MySQL function date_format to format it before its returned[/color]
but I[color=blue]
> use a simply query (e.g. SELECT * FROM TABLE WHERE colum =[/color]
'$somevalue') and[color=blue]
> a while loop to read all the variables in, so I don't think I can[/color]
format it[color=blue]
> before its returned.
>
> $date1 = "2005-04-12 20:45:54";[/color]


Use the function strtotime() <http://www.php.net/strtotime>
echo '<br>Date is: ' . date('j-M-y',strtotime($date1));

Ken

re: MySQL DATETIME and PHP Dates

Tony Clarke wrote:[color=blue]
>
> Is there an easy way of formatting a MySQL DATETIME field in PHP.[/color]

Yes. Check out the strtotime() function:

http://www.php.net/strtotime

Cheers,
NC

re: MySQL DATETIME and PHP Dates

You could use the MySQL function UNIX_TIMESTAMP(column) to return a
PHP-compatible UNIX timestamp, which you could then pass as the second
parameter of the PHP date() function.

For example:

$row = mysql_fetch_assoc(mysql_query("SELECT UNIX_TIMESTAMP(datetime)
AS datetime_u FROM table LIMIT 1"));
$date = date('j-M-y', $row['datetime_u']);

re: MySQL DATETIME and PHP Dates

Tony Clarke wrote on Wednesday 06 Apr 2005 23:37:[color=blue]
> Is there an easy way of formatting a MySQL DATETIME field in PHP.[/color]

I've just solved this problem myself:

function reformatDate($datetime) {
// put date in universal format, no seconds
list($year, $month, $day, $hour, $min, $sec) = split( '[: -]',
$datetime);
return "$year-$month-$day at $hour:$min";
}


$dateA=$row['date'];
$theDate=reformatDate($dateA);

Watch the line wrap in there!!

HTH,
- QS Computing.

--
QS Computing
http://www.qscomputing.plus.com
postmaster@qscomputing.plus.com

re: MySQL DATETIME and PHP Dates

On 2005-04-07, QS Computing <postmaster@qscomputing.plus.com> wrote:[color=blue]
> Tony Clarke wrote on Wednesday 06 Apr 2005 23:37:[color=green]
>> Is there an easy way of formatting a MySQL DATETIME field in PHP.[/color]
>
> I've just solved this problem myself:
>
> function reformatDate($datetime) {
> // put date in universal format, no seconds
> list($year, $month, $day, $hour, $min, $sec) = split( '[: -]',
> $datetime);
> return "$year-$month-$day at $hour:$min";
> }
>
>
> $dateA=$row['date'];
> $theDate=reformatDate($dateA);[/color]

Or you could use the builtin functions for date/time handling:

$timestamp = "2005-04-06 15:43:34";
$time = strtotime($timestamp);
print date('Y-m-d \a\t H:i', $time)."\n";

The strtotime() function will parse the input and give you a unix
timestamp. The date() function is used to format unix timestamps in a
variety of ways.

--
Cheers
- Jacob Atzen

re: MySQL DATETIME and PHP Dates

Jacob Atzen wrote on Thursday 07 Apr 2005 19:25:[color=blue]
> Or you could use the builtin functions for date/time handling[/color]

Hadn't even thought of that. Presumably using builtins speeds it up - I'll
change my code now.

Thanks,
- QS Computing.

--
QS Computing
http://www.qscomputing.plus.com
postmaster@qscomputing.plus.com

re: MySQL DATETIME and PHP Dates

On 2005-04-07, QS Computing <postmaster@qscomputing.plus.com> wrote:[color=blue]
> Jacob Atzen wrote on Thursday 07 Apr 2005 19:25:[color=green]
>> Or you could use the builtin functions for date/time handling[/color]
>
> Hadn't even thought of that. Presumably using builtins speeds it up -
> I'll change my code now.[/color]

Maybe so, maybe not. I doubt you'll be formatting that many dates that
it will actually make a difference speedwise. But it does make it easier
to parse and format dates in a variety of formats.

--
Cheers
- Jacob Atzen