MySQL DATETIME and PHP Dates

[Tony Clarke]

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.

[Ken Robinson]

Tony Clarke wrote:

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";

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

Ken

[NC]

Tony Clarke wrote:

Is there an easy way of formatting a MySQL DATETIME field in PHP.

Yes. Check out the strtotime() function:

http://www.php.net/strtotime

Cheers,
NC

[Jasper Bryant-Greene]

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']);

[QS Computing]

Tony Clarke wrote on Wednesday 06 Apr 2005 23:37:

Is there an easy way of formatting a MySQL DATETIME field in PHP.

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
po********@qscomputing.plus.com

[Jacob Atzen]

On 2005-04-07, QS Computing <po********@qscomputing.plus.com> wrote:

Tony Clarke wrote on Wednesday 06 Apr 2005 23:37:

Is there an easy way of formatting a MySQL DATETIME field in PHP.

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);

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

[QS Computing]

Jacob Atzen wrote on Thursday 07 Apr 2005 19:25:

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

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
po********@qscomputing.plus.com

[Jacob Atzen]

On 2005-04-07, QS Computing <po********@qscomputing.plus.com> wrote:

Jacob Atzen wrote on Thursday 07 Apr 2005 19:25:

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

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

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